Re: 3 way outer join dilemma

Nice shot, but unfortunately that does not work.

fid can occur mutliple times in t1
vid can occur multiple times in t2

for a pair (fid,vid) there are 1 or 0 occurences in t3.

doh!

Anyway, although I have not had time to implement it, I think the solution
from Roger Hand will work.

Thanks for the effort!  :)

Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of scott.marlowe
> Sent: Tuesday, August 19, 2003 3:52 PM
> To: terry@ashtonwoodshomes.com
> Cc: 'Postgres (E-mail)'
> Subject: Re: [GENERAL] 3 way outer join dilemma
>
>
> On Tue, 19 Aug 2003 terry@ashtonwoodshomes.com wrote:
>
> > Uh, sorry.  Explicitly stating t1.fid = X and t2.vid = Y
> was to imply that,
> > but a better example would be:
> >
> > SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
> > FROM t1, t2, t3
> > WHERE t1.fid = X
> >   AND t2.vid = Y
> >   AND t1.id = t2.id
> >   AND t3.fid = t1.fid
> >   AND t3.vid = t2.vid
>
> Is this t1.id = t2.id AND t3.fid = t1.fid AND t3.vid = t2.vid
> something that where all three will be true anytime any two
> are true?
> I.e. there's maybe no exact need for the cross from t3 to t2
> or whatever?
>
> I.e. if t1.id = t2.id, and t3.fid = t1.fid, then by
> definition must t3.vid
> = t2.vid?  If the corresponding row in t3 is empty then  we
> have nulls and
> all we're worried about is t1 and t2.  Is t2 dependent on t1?
>
> I'm guessing there's no need for t3.vid = t2.vid unless your data got
> knackered, in which case:
>
> SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
> FROM t1 join t2 on (t1.id = t2.id)
> left join t3 on (t1.fid = t3.fid)
>
> should do it.  Or can count(t3) be >1 for each row referenced
> in t2 or
> anything odd like that and that's why there's t3.vid = t2.vid???
>
> More questions than answers, I know.  :-)
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so
> that your
>       message can get through to the mailing list cleanly
>