Search code examples
sqlsql-serversubquerywindow-functions

Select data from 1 column based on multiple conditions in 2nd column

I need to exclude all IDs that have the color green in them.

ID Colors_Allowed
555 Blue
555 Green
666 Blue
786 Blue
888 Blue
888 Green
888 Red
999 Red
999 Orange

Expected result:

ID Colors_allowed
666 Blue
786 Blue
999 Red
999 Orange

Here's the attempt I've made:

SELECT *
FROM Table
WHERE colors_allowed != 'Green'

But this also gives me ids "555" and "888" with colors other than green, which I don't want since they had the color green, and that should have disqualified them.

Solution

  • You can use INNER subquery to find ID having color Green and using outer query with NOT IN clause to select ID which does not have Green in it.

    Demo query - http://sqlfiddle.com/#!18/2848d/1

    SELECT DISTINCT ID, Colors_Allowed
FROM TableName
WHERE ID NOT IN  ( SELECT ID
                   FROM TableName
                   WHERE Colors_Allowed = 'Green');