Find rows matching only values from a specific list with SQL

I have a table like this:

Id    Status
  --    -----------
  1     Closed
  1     In progress
  2     Rejected
  3     Cancelled
  4     Rejected
  4     Closed
  5     In progress

I want to show rows that have the Status of closed, rejected or cancelled. However, I want to exclude the IDs that have a different status value, even if they also have any of those three.

So, for my example I would like the output to be this:

Id    Status
  --    ---------
  2     Rejected
  3     Cancelled
  4     Rejected
  4     Closed

What should my query look like?

One way would be to use NOT EXISTS.

SELECT *
FROM   YourTable T1
WHERE  Status IN ( 'Closed', 'Rejected', 'Cancelled' )
       AND NOT EXISTS (SELECT *
                       FROM   YourTable T2
                       WHERE  T2.Id = T1.Id
                              AND T2.Status NOT IN ( 'Closed', 'Rejected', 'Cancelled' )) ;

Or an alternative method if your DBMS supports windowed aggregates..

SELECT Id,
       Status
FROM   (SELECT *,
               COUNT(CASE WHEN Status NOT IN ('Closed','Rejected','Cancelled') 
                             THEN 1 END) 
                     OVER (PARTITION BY Id) C
        FROM   YourTable) D
WHERE  Status IN ( 'Closed', 'Rejected', 'Cancelled' )
       AND C = 0 ;

And another option using EXCEPT:

SELECT * 
FROM YourTable 
WHERE Id IN 
      ( SELECT Id FROM YourTable 
        WHERE Status IN ( 'Closed', 'Rejected', 'Cancelled' ) 
      EXCEPT 
        SELECT Id FROM YourTable 
        WHERE Status NOT IN ( 'Closed', 'Rejected', 'Cancelled' )
      ) ;

StackExchange Database Administrators Q#140922, answer score: 8