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!

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

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

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

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

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

"Bill Karwin" wrote in message
news:dr38cd025je@enews4.newsguy.com...
> "Notgiven" 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.

Thanks so MUCH!

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

"Bill Karwin" wrote in message
news:dr38cd025je@enews4.newsguy.com...
> "Notgiven" 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.

Thanks so MUCH!