|
|
Posted by Notgiven on 01/23/06 23:43
"Bill Karwin" <bill@karwin.com> wrote in message
news:dr38cd025je@enews4.newsguy.com...
> "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.
Thanks so MUCH!
Navigation:
[Reply to this message]
|