CODEWITHSUNDEEP
sqlsql-serversql-server-2012
user3746310
user3746310

Reputation: 1

Select Certain Groups while Excluding Others

I've made a small dataset similar to what I need. It has the following: 

ID  Color
2   blue
2   red
3   blue
3   yellow
4   white
4   green
4   blue
5   green
5   purple
5   black

I want to select the grouped IDs but exclude those that contain 'white' or 'black'. So, in other words, IDs 2 and 3 should show. But 4 and 5 should not show because they have 'white' and/or 'black' within their Color column.

Upvotes: 0

Views: 62

Answers (4)

M.Ali
M.Ali

Reputation: 69514

SELECT t.ID
FROM @TABLE t
WHERE NOT EXISTS (
                  SELECT 1 
                  FROM @TABLE
                  WHERE t.ID = ID 
                   AND (color = 'white' or color = 'Black') 
                  )
GROUP BY t.ID

WORKING SQL FIDDLE

Upvotes: 0

Simon
Simon

Reputation: 1416

SELECT *
FROM colortable
WHERE ID NOT IN (
    SELECT ID
    FROM colortable
    WHERE Color IN ('white', 'black')  
    )

Upvotes: 2

Satwik Nadkarny
Satwik Nadkarny

Reputation: 5135

You can try this:

SELECT     DISTINCT ID
FROM       TABLE
WHERE      Color NOT IN ('white', 'black')

Hope this helps!!!

Upvotes: 0

juergen d
juergen d

Reputation: 204756

Sum up how many times these forbidden value appear in your groups. Select only the ones where this sum is zero

select id
from your_table
group by id
having sum(case when color in ('white','black') then 1 else 0 end) = 0

Upvotes: 2

Related Questions