469,578 Members | 1,325 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

help with SQL coding question - 3 tables with outer join needed

I have three tables:

table1:
table2_ID
table3_ID
complete

table3:
table3_ID
name

table2:
table2_ID
table4_ID

Given table3.table3_ID, I need to retrieve the value of table1.complete OR
"Not Complete".

I have tried this in several different iterations without success.

SELECT
IF(ISNULL(e.complete), e.complete,'Not Complete') as complete
FROM table3 s
RIGHT OUTER JOIN table1 e ON e.table3_ID = s.table3_ID
INNER JOIN table2 t ON t.table2_ID = e.table2_ID
WHERE s.table3_ID = 2993
AND t.table4_ID = 10029

Any ideas are much appreciated!
Jan 23 '06 #1
2 1644
"Notgiven" <no*********@invalid.invalid> wrote in message
news:aT*****************@bignews1.bellsouth.net...
I have three tables:

table1:
table2_ID
table3_ID
complete

table3:
table3_ID
name

table2:
table2_ID
table4_ID

Given table3.table3_ID, I need to retrieve the value of table1.complete OR
"Not Complete".

I have tried this in several different iterations without success.

SELECT
IF(ISNULL(e.complete), e.complete,'Not Complete') as complete
FROM table3 s
RIGHT OUTER JOIN table1 e ON e.table3_ID = s.table3_ID
INNER JOIN table2 t ON t.table2_ID = e.table2_ID
WHERE s.table3_ID = 2993
AND t.table4_ID = 10029


I think you have RIGHT OUTER JOIN when you mean LEFT OUTER JOIN. You're
trying to find real values in s, even when there is no matching value in e.
But the RIGHT OUTER JOIN in the order you are using it above is the reverse
of that -- all rows of e, and show NULLs in s if there are no matching rows.

Also, by using WHERE t.table4_ID = 10029, you've limited the query to rows
where you have values in t, and therefore because you've used INNER JOIN,
there must be values in e. So you've omitted the cases where the outer join
gives you NULLs.

I'd do it this way:

SELECT COALESCE(e.complete, 'Not Complete')
FROM table3 AS s
LEFT OUTER JOIN table1 AS e ON e.table3_ID = s.table3_ID
LEFT OUTER JOIN table2 AS t ON t.table2_ID = e.table2_ID AND t.table4_ID =
10029
WHERE s.table3_ID = 2993

This may not be exactly what you intended; I can't tell from your
description whether you want all rows of e that match s, or only rows of e
that match both s and the subset of rows in t matching 10029.

Regards,
Bill K.
Jan 23 '06 #2
"Bill Karwin" <bi**@karwin.com> wrote in message
news:dr*********@enews4.newsguy.com...
"Notgiven" <no*********@invalid.invalid> wrote in message
news:aT*****************@bignews1.bellsouth.net...
I have three tables:

table1:
table2_ID
table3_ID
complete

table3:
table3_ID
name

table2:
table2_ID
table4_ID

Given table3.table3_ID, I need to retrieve the value of table1.complete
OR "Not Complete".

I have tried this in several different iterations without success.

SELECT
IF(ISNULL(e.complete), e.complete,'Not Complete') as complete
FROM table3 s
RIGHT OUTER JOIN table1 e ON e.table3_ID = s.table3_ID
INNER JOIN table2 t ON t.table2_ID = e.table2_ID
WHERE s.table3_ID = 2993
AND t.table4_ID = 10029


I think you have RIGHT OUTER JOIN when you mean LEFT OUTER JOIN. You're
trying to find real values in s, even when there is no matching value in
e. But the RIGHT OUTER JOIN in the order you are using it above is the
reverse of that -- all rows of e, and show NULLs in s if there are no
matching rows.

Also, by using WHERE t.table4_ID = 10029, you've limited the query to rows
where you have values in t, and therefore because you've used INNER JOIN,
there must be values in e. So you've omitted the cases where the outer
join gives you NULLs.

I'd do it this way:

SELECT COALESCE(e.complete, 'Not Complete')
FROM table3 AS s
LEFT OUTER JOIN table1 AS e ON e.table3_ID = s.table3_ID
LEFT OUTER JOIN table2 AS t ON t.table2_ID = e.table2_ID AND t.table4_ID
= 10029
WHERE s.table3_ID = 2993

This may not be exactly what you intended; I can't tell from your
description whether you want all rows of e that match s, or only rows of e
that match both s and the subset of rows in t matching 10029.

Regards,
Bill K.


Thanks so MUCH!
Jan 23 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by netpurpose | last post: by
3 posts views Thread by Prem | last post: by
28 posts views Thread by stu_gots | last post: by
5 posts views Thread by khan | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.