Re: 3 way outer join dilemma - Mailing list pgsql-general
From | |
---|---|
Subject | Re: 3 way outer join dilemma |
Date | |
Msg-id | 010801c3666c$dca7ee40$2766f30a@development.greatgulfhomes.com Whole thread Raw |
In response to | Re: 3 way outer join dilemma ("scott.marlowe" <scott.marlowe@ihs.com>) |
Responses |
Re: 3 way outer join dilemma
(Ron Johnson <ron.l.johnson@cox.net>)
Re: 3 way outer join dilemma ("scott.marlowe" <scott.marlowe@ihs.com>) |
List | pgsql-general |
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 >
pgsql-general by date: