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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@jim
your query returns error
no group by in query...
also logically not correct if you just add status=1
your query returns error
no group by in query...
also logically not correct if you just add status=1
Solution:
Creating test table @temp:
Output:
select distinct ID, Status From @temp
Where Status =1;
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)
Output:
ID Status
1 1
2 1
4 1
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Jim, got an error with your air code but I had to give something for the "do my own stunts" comment! :D
Open in new window