Re: 3 way outer join dilemma

That works perfectly, of course.

Thanks a million!

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

> -----Original Message-----
> From: Roger Hand [mailto:rhand(at)ragingnet(dot)com]
> Sent: Tuesday, August 19, 2003 1:21 PM
> To: Roger Hand; terry(at)ashtonwoodshomes(dot)com
> Cc: Postgres (E-mail)
> Subject: RE: [GENERAL] 3 way outer join dilemma
>
>
> Oops, forgot the where clause:
>
> SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
> FROM t1
> INNER JOIN t2
> ON t1.id = t2.id
> LEFT OUTER JOIN t3
> ON t1.fid = t3.fid AND t2.vid = t3.vid
> WHERE t1.fid = X AND t2.vid = Y
>
> -----Original Message-----
> From: Roger Hand
> Sent: Tuesday, August 19, 2003 10:19 AM
> To: 'terry(at)ashtonwoodshomes(dot)com'
> Cc: 'Postgres (E-mail)'
> Subject: RE: [GENERAL] 3 way outer join dilemma
>
>
> Tested and works the way I understand you want it to:
>
> SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
> FROM t1
> INNER JOIN t2
> ON t1.id = t2.id
> LEFT OUTER JOIN t3
> ON t1.fid = t3.fid AND t2.vid = t3.vid
>
> -----Original Message-----
> From: terry(at)ashtonwoodshomes(dot)com [mailto:terry(at)ashtonwoodshomes(dot)com]
> Sent: Tuesday, August 19, 2003 9:14 AM
> To: 'scott.marlowe'
> Cc: 'Postgres (E-mail)'
> Subject: Re: [GENERAL] 3 way outer join dilemma
>
>
> 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
>
> Now, I discover that the record in t3 may not always exist,
> so somehow I want to do an outer join...
>
> SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
> FROM t1, t2, OUTER JOIN t3 ON (t3.fid = t1.fid AND t3.vid = t2.vid)
> WHERE t1.fid = X
> AND t2.vid = Y
> AND t1.id = t2.id
>
> Thanks
>
> Terry Fielder
> Manager Software Development and Deployment
> Great Gulf Homes / Ashton Woods Homes
> terry(at)greatgulfhomes(dot)com
> Fax: (416) 441-9085
>
>
> > -----Original Message-----
> > From: pgsql-general-owner(at)postgresql(dot)org
> > [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of
> scott.marlowe
> > Sent: Tuesday, August 19, 2003 11:55 AM
> > To: terry(at)ashtonwoodshomes(dot)com
> > Cc: Postgres (E-mail)
> > Subject: Re: [GENERAL] 3 way outer join dilemma
> >
> >
> > On Mon, 18 Aug 2003 terry(at)ashtonwoodshomes(dot)com wrote:
> >
> > > Here's what I have (simplified)
> > >
> > > 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 t3.fid = t1.fid
> > > AND t3.vid = t2.vid
> > >
> > > Now, I discover that the record in t3 may not always exist,
> > so somehow I
> > > want to do an outer join...
> > >
> > > SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
> > > FROM t1, t2, OUTER JOIN t3 ON (t3.fid = t1.fid AND t3.vid
> = t2.vid)
> > > WHERE t1.fid = X
> > > AND t2.vid = Y
> > >
> > > But I get the statement that "t1 is not part of JOIN"
> >
> > If t3 may not have a record, then how can you be using it
> to join t2 ?
> >
> > It seems that if t3 doesn't exist, then of course "AND t3.vid
> > = t2.vid" is
> > going to be "AND NULL = t2.vid" which of course, will always
> > be false,
> > i.e. you'll never be able to join t2. Is there a common key
> > between t2
> > and t1?
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo(at)postgresql(dot)org)
>