CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2012
Polynomial Proton
Polynomial Proton

Reputation: 5135

SQL Server - Get Values based on ID where another column doesnt have all values

This might be an easy one, but i'm not too familiar with sql server and advanced usages. Basically I am unable to form a query for this.

I have a table which looks like below. Each ID has a type which has only 3 values (0,1,2). 

ID      Type
40271   0
40271   1
40271   2
40281   0
40281   1

I want to select only the IDs which DO NOT have all the Types.

Output(Since its missing the 3rd type i.e 2):

    ID      Type
    40281   0
    40281   1

I need to select all rows from table where for each ID, all types(0,1,2) do not exist.

I tried group by, having etc clauses but couldnt quite reach there.

I tried this, but it gives me both the IDs

select ID 
    from Table
    group by ID
    having count(distinct Type) > 1

Upvotes: 0

Views: 3750

Answers (2)

ZLK
ZLK

Reputation: 2874

One way to do it is using EXISTS to check whether the ID has less than three types.

SELECT ID, Type
FROM Table T
WHERE EXISTS (
    SELECT 1
    FROM Table
    WHERE ID = T.ID
    GROUP BY ID
    HAVING COUNT(DISTINCT Type) < 3);

Upvotes: 2

EugeneK
EugeneK

Reputation: 2224

Try this

select t.* from test1 t, 
  (select id, count(*) cnt from test1 group by id) t1
where t.id = t1.id
   and t1.cnt < 3

Upvotes: -1

Related Questions