CODEWITHSUNDEEP
sql-server
YuriNator
YuriNator

Reputation: 5

SQL Help - Find all distinct IDs that are not equal to a value - There are multiple IDs that are the same

I am trying to return the IDs in a table where the IDs do not contain a value in another column. There are multiple IDs in the table that are the same, however the second column contains a different value. I want to return all of the distinct IDs where the second column does not contain a value of 10.

For example my data table looks like:

ID | Value
32 |   8
32 |   9
32 |  10
35 |   8
35 |   9

As per the table above I want to return all the IDs that do not contain 10, however when I run the following script I am getting back a result that gives me all the values that contain 8 & 9 and only omits 10 when I want just the distinct ID that have no rows that all contain 10 in the Values column.

I am also joining to another table.

select distinct table1.ID, table2.Values
from table1
left join table2 on table1.ID=table2.ID
where table2.Values <> 10

Upvotes: 0

Views: 162

Answers (1)

Thom A
Thom A

Reputation: 95554

One method would be a HAVING:

SELECT ID
FROM dbo.YourTable
GROUP BY ID
HAVING COUNT(CASE [Value] WHEN 10 THEN 1 END) = 0;

Upvotes: 1

Related Questions