• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

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
0
websss
Asked:
websss
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:
select SSNumber  
from tbl t
where NOT EXISTS(select NULL from tbl t2 where t2.status =1 and t2.SSNumber  = t1.SSNumber ) 

Open in new window

0
 
awking00Commented:
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)
0
 
John_VidmarCommented:
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

0
 
websssAuthor Commented:
Correct social security number is unique
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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?
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Tackle projects and never again get stuck behind a technical roadblock.
Join Now