Re: 3 way outer join dilemma - Mailing list pgsql-general
From | |
---|---|
Subject | Re: 3 way outer join dilemma |
Date | |
Msg-id | 007901c3684b$cf7e4260$2766f30a@development.greatgulfhomes.com Whole thread Raw |
In response to | Re: 3 way outer join dilemma ("Roger Hand" <rhand@ragingnet.com>) |
List | pgsql-general |
That works perfectly, of course. Thanks a million! Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes terry@greatgulfhomes.com Fax: (416) 441-9085 > -----Original Message----- > From: Roger Hand [mailto:rhand@ragingnet.com] > Sent: Tuesday, August 19, 2003 1:21 PM > To: Roger Hand; terry@ashtonwoodshomes.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@ashtonwoodshomes.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@ashtonwoodshomes.com [mailto:terry@ashtonwoodshomes.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@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 11:55 AM > > To: terry@ashtonwoodshomes.com > > Cc: Postgres (E-mail) > > Subject: Re: [GENERAL] 3 way outer join dilemma > > > > > > On Mon, 18 Aug 2003 terry@ashtonwoodshomes.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@postgresql.org) >
Attachment
pgsql-general by date: