Link to home
Start Free TrialLog in
Avatar of ttist25
ttist25

asked on

SQL 2008 - Find rows where only a specified field value exists for all rows with the same ID

Hopefully the title will convey what I'm trying to do but:

Given a table similar to the following:
--------------------
| ID | Status  |
--------------------
| 1  |      1      |
| 1  |      2      |
| 2  |      1      |
| 2  |      1      |
| 3  |      2      |
| 3  |      2      |
| 4  |      1      |
--------------------

How can I select the distinct IDs that only have a Status value of 1 for all rows in the table?
My result set would only contain IDs 2 and 4 (because 1 is the only Status value in all of their rows).

TIA!
SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
with myTable as (
select 1 id, 1 status
union select 1,2
union select 2,1
union select 2,2
union select 3,2
union select 3,2
union select 4,1
)
select id from myTable
 group by id
having sum(case when status=1 then 1 else 0 end)=1
   and count(1)=1

id
4

Open in new window

@jim

your query returns error
no group by in query...

also logically not correct if you just add status=1
Solution:
select distinct ID, Status From @temp
Where Status =1;

Open in new window


Creating test table @temp:
use ee
declare @temp table (ID int, Status int);

insert into @temp(ID, Status) values
(1, 1)
,(1, 2)
,(2, 1)
,(2, 1)
,(3, 2)
,(3, 2)
,(4, 1)

Open in new window


Output:
ID  Status
1    1
2    1
4    1

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ttist25
ttist25

ASKER

Thanks guys! Works great.  

Jim, got an error with your air code but I had to give something for the "do my own stunts" comment!  :D