asked on

SQL where OR clause

Hi
I have the following results

Name Status date
------- ------- ------
mark 1 29/03/2014
mark 0 09/03/2014
mark 1 19/03/2014
John 0 19/03/2014
bob 1 19/03/2014

I have a the SQL
select SSNumber from tb2
where SSNumber NOT IN (select SSNumber from tbl where status =1)

But as MARK has status 0 and 1 it is showing in the list
Really i want this

if MARK has only a status 1 then it should NOT show
If MARK has Status 1 and 0 then it should NOT show
If MARK only has status 0 then it should show

What SQL would i need to do this
I guess it would be something like
select SSNumber from tbl where status =1 (or occurrence of status = 0 and another with status = 1)

hope that makes sense
this could be super simple but really tired and my brain hurts

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

Your query just needs a slight modification -
select SSNumber from tb2
WHERE status = 0 AND
SSNumber NOT IN (select SSNumber from tbl where status =1)

John_Vidmar

Assuming SSNumber is unique for a person (i.e., it's not primary-key):

select	SSNumber
from	tb2
group
by	SSNumber
having	max(status) = 0

Open in new window

websss

ASKER

Correct social security number is unique

Guy Hengel [angelIII / a3]

how can that value of SSN be unique (in that table) if you have several rows for the same person? I don't think that that field is the primary key in that table?
still, if you only need the SSN value, John's suggestion is perfectly valid.

other than that: does my suggestion work? if not, what are the example where it does not work?