3 way outer join dilemma

[terry]

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
te***@greatgulfhomes.com
Fax: [phone]
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Stephan Szabo]

On Mon, 18 Aug 2003 te***@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 ...

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[scott.marlowe]

On Mon, 18 Aug 2003 te***@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