Posted by Bill Karwin on 01/23/06 20:47

"Notgiven" <notreallyme@invalid.invalid> wrote in message
news:aT8Bf.9921$TK2.2986@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.

[Back to original message]