SQL Server : SELECT DISTINCT [COL1] from {TABLE} WHERE [COL2] = 'A' AND [COL2] <> 'B'

Question

I have to find a SELECT DISTINCT [COL1] from {TABLE} there can find all from [COL1] there have 'A' in [COL2] but not 'B' in [COL2] In this case '3' and '4' should be the result

TABLE

   [COL1]      [COL2]
   ----        ----
   '1'          'A'
   '1'          'B'
   '1'          'C'
   '2'          'B'
   '2'          'C'
   '3'          'A'
   '3'          'C'
   '4'          'A'
   '4'          'D'

Answer 1

SELECT [COL1] from {TABLE} WHERE [COL2] = 'A' 
EXCEPT
SELECT [COL1] from {TABLE} WHERE [COL2] = 'B'

Or

SELECT [COL1]
FROM   {TABLE}
GROUP  BY [COL1]
HAVING MAX(CASE
             WHEN [COL2] = 'A' THEN 1
             ELSE 0
           END) = 1
       AND MAX(CASE
                 WHEN [COL2] = 'B' THEN 1
                 ELSE 0
               END) = 0