470,647 Members | 1,126 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,647 developers. It's quick & easy.

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

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

Nov 11 '05 #1
2 1507

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

Nov 11 '05 #2
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

Nov 11 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Martin | last post: by
8 posts views Thread by Matt | last post: by
3 posts views Thread by Martin | last post: by
1 post views Thread by Andreas Bauer | last post: by
5 posts views Thread by Eitan M | last post: by
reply views Thread by warner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.