CODEWITHSUNDEEP
sqlsql-serverdistinct
JimmyPop13
JimmyPop13

Reputation: 317

Returning rows with the same ID but exclude some on second column

I've seen similar questions about but not quite hitting the nail on the head for what I need. Lets say I have a table.

+-----+-------+
| ID  | Value |
+-----+-------+
| 123 |     1 |
| 123 |     2 |
| 123 |     3 |
| 456 |     1 |
| 456 |     2 |
| 456 |     4 |
| 789 |     1 |
| 789 |     2 |
+-----+-------+

I want to return DISTINCT IDs but exclude those that have a certain value. For example lets say I don't want any IDs that have a 3 as a value. My results should look like.

+-----+
| ID  | 
+-----+
| 456 |
| 789 |
+-----+

I hope this makes sense. If more information is needed please ask and if this has been answered before please point me in the right direction. Thanks.

Upvotes: 0

Views: 1132

Answers (4)

Fahmi
Fahmi

Reputation: 37473

Try this:

select id from tablename
group by id
having (case when value=3 then 1 else 0 end)=0

Upvotes: 1

Eralper
Eralper

Reputation: 6612

You can also use EXCEPT for comparing following two data sets that will give the desired result set

select distinct Id from ValuesTbl
except
select Id from ValuesTbl where Value = 3

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use not exists :

select distinct t.id
from table t
where not exists (select 1 from table t1 where t1.id = t.id and t1.value = 3);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use group by and having:

select id
from t
group by id
having sum(case when value = 3 then 1 else 0 end) = 0;

The having clause counts the number of "3"s for each id. The = 0 returns only returns groups where the count is 0 (i.e. there are no "3"s).

Upvotes: 2

Related Questions