Re: 3 way outer join dilemma - Mailing list pgsql-general

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)