"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.
