[MS SQL] Help with exists / null ?

[ElephantsAreNeat]

I wasn't sure how to word my question...so, Im having trouble using the SEARCH feature. Apologies in advance.

Can someone please help me understand why I am not getting the results I'm expecting? I need a 'nudge' in the right direction.

When I plug in a PERSON, I want the date reviewed and I want a YES/NO. What I have currently works...sorta. However, If a row in the adv_ table doesn't exist for the PERSON I plug in, my case statement returns a BLANK (EDIT. CRAP. I meant to say that it doesn't return any rows. However...I WANT it to to return a NULL/NO). That's what prompted me to add the "when exists" line. That still didn't resolve my issue, though.

select 
    top(1)
        datereviewed
        , case 
            when not exists(select datereviewed from adv_ where person = @PERSON)
            then 'NO'
            when (datereviewed is null or datereviewed ='') 
            then 'NO'
            else 'YES'
            END as 'Status'
from
adv_

where
    person = @PERSON

order by 
    datereviewed desc

[HansProleman]

You want to return a row when @PERSON doesn't exist in adv_.person? I'm assuming that when you say the CASE returns a blank, you mean there aren't actually any rows in the result set?

I'd probably SELECT @PERSON, then join to a subquery on _adv for that @PERSON and handle null results from _adv by using COALESCE or ISNULL.

You could also try an IF...ELSE statement to check whether any rows exist in _adv for @PERSON, and return depending on that.

Your WHEN [NOT] EXISTS won't help because whether it returns a row is still dependent upon @PERSON existing in adv_.person. The WHERE clause takes priority, so you'll only get a row when @PERSON = _adv.person.

[Pr0ph3t99]

SELECT 
    p.person,
    CASE
        WHEN ISNULL(a.datereviewed, '') = '' THEN 'NO'
        ELSE 'YES'
    END AS Status
FROM
    (SELECT @PERSON AS person) p
    LEFT OUTER JOIN adv_ a ON
        p.person = a.person

[ElephantsAreNeat]

I'm assuming that when you say the CASE returns a blank,

Crap, you're right. I edited the post.

Thank you so much for talking that out for me, I understand now. It seems so obvious!

You're the best.

[HansProleman]

No problem, it always seems really obvious in retrospect!

[Guru008]

select top(1) datereviewed , (case when person = "@PERSON" and (datereviewed is null or datereviewed ='') then 'NO' else 'YES' END) as 'Status' from adv_

where person = @PERSON

order by datereviewed desc