Thread: 3 way outer join dilemma
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" Is there some way that I can merge t1 and t2 together, or do I have to do a subselect (ugh) as the only viable alternative? Any ideas is appreciated... Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes terry@greatgulfhomes.com Fax: (416) 441-9085
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 Maybe: FROM t1 CROSS JOIN t2 LEFT OUTER JOIN t3 ON ...
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?
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 >
On Tue, 2003-08-19 at 11:13, 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
>
> 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
How about:
SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
FROM (t1 join t2 on t1.id = t2.id)
LEFT OUTER JOIN t3 on (t3.fid = t1.fid AND t3.vid = t2.vid)
WHERE t1.fid = X
AND t2.vid = Y
;
> 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?
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA
"Man, I'm pretty. Hoo Hah!"
Johnny Bravo
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)
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)
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. :-)
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 >
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) >