By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,301 Members | 1,341 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,301 IT Pros & Developers. It's quick & easy.

3 way outer join dilemma

P: n/a
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
Share this Question
Share on Google+
2 Replies

P: n/a

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

P: n/a
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.