Select based on specific condition

Question

If someone could update the title to better reflect my question that would be much appreciated. I think I've struggled to find the answer somewhat because I don't know the right search terms.

If I have the following values in a table:

TableID      TableSensitivity
-----------------------------
1            aSensitivity
1            aRevision
2            bRevision
3            cRevision
3            cSensitivity

How can I filter the values containing "Revision" only for each TableID where the is already a value containing "Sensitivity". This is the expected output:

TableID      TableSensitivity
-----------------------------
1            aSensitivity
2            bRevision
3            cSensitivity

Here's an example setup:

DECLARE  @Example TABLE (TableID INT, TableSensitivity VARCHAR(200))

INSERT INTO @Example(TableID, TableSensitivity) VALUES (1, 'aSensitivity')
INSERT INTO @Example(TableID, TableSensitivity) VALUES (1, 'aRevision')
INSERT INTO @Example(TableID, TableSensitivity) VALUES (2, 'bRevision')
INSERT INTO @Example(TableID, TableSensitivity) VALUES (3, 'cSensitivity')
INSERT INTO @Example(TableID, TableSensitivity) VALUES (3, 'cRevision')

I've tried using CASE to determine the rows with sensitivities and figured that I could use a MAX to filter, but couldn't get it working:

SELECT TableID
     , TableSensitivity
     , CASE WHEN TableSensitivity LIKE '%Sensitivity' THEN 1 ELSE 0 END 'SomeKey' 
FROM @Example

Answer 1

You could use a WHERE NOT EXISTS

SELECT TableID
     , TableSensitivity
FROM @Example e1
WHERE NOT EXISTS
(
    SELECT *
    FROM @Example e2
    WHERE e1.TableID=e2.TableID
    AND e2.TableSensitivity LIKE '%Sensitivity'
    AND e1.TableSensitivity LIKE '%Revision'
)