Perfect. That also makes the next logical permutation of adding another status to the acceptable statuses super easy. For example, if someone later decides that the status of 'C' and or 'T' is acceptable, then a very small change is all that is needed.

DROP TABLE #TestTable

--===== Create and populate a test table on-the-fly.

-- This is NOT a part of the solution. We're just building a test table here.

SELECT *

INTO #TestTable

FROM (

SELECT '905055' , 1,'222-63-05' ,'C' UNION ALL

SELECT '905055' , 2,'222-63-47' ,'C' UNION ALL

SELECT '905055' , 3,'222-63-80' ,'C' UNION ALL

SELECT '905055' , 4,'222-23-72' ,'O' UNION ALL

SELECT 'AW00000253', 1,'222-63-147','C' UNION ALL

SELECT 'AW00000253', 2,'222-63-247','C' UNION ALL

SELECT 'AW00000306', 2,'222-23-31' ,'O' UNION ALL

SELECT 'AW00000306',10,'222-23-72' ,'O' UNION ALL

SELECT 'AW00000306',12,'222-23-47' ,'O' UNION ALL

SELECT 'SF00000389', 1,'222-23-11' ,'C' UNION ALL

SELECT 'SF00000390', 2,'222-23-11' ,'C' UNION ALL

SELECT 'MI12345678', 1,'AAA-BB-CC' ,'C' UNION ALL --Added data here down

SELECT 'MI12345678', 2,'AAA-BB-CC' ,'T' UNION ALL

SELECT 'MI12345678', 3,'AAA-BB-CC' ,'A' UNION ALL

SELECT 'RI87654321', 1,'AAA-BB-DD' ,'C' UNION ALL

SELECT 'RI87654321', 2,'AAA-BB-DD' ,'T' UNION ALL

SELECT 'CA99999999', 2,'AAA-BB-EE' ,'T'

) tt (CoNum, CoLine, ItemNum, CoItemStat)

;

--===== Create an index to boost performance

CREATE INDEX IX_TestTable_CoNum_CoItemStat

ON #TestTable (CoNum, CoItemStat)

;

--===== Return only those lines where all the CoItemStat's = 'C' or 'T' for each CoNum

-- Notice the second "NOT IN"

SELECT *

FROM #TestTable

WHERE CoNum NOT IN (SELECT CoNum FROM #TestTable WHERE CoItemStat NOT IN ('C','T'))

;

Results...

CoNum CoLine ItemNum CoItemStat

---------- ----------- ---------- ----------

AW00000253 1 222-63-147 C

AW00000253 2 222-63-247 C

SF00000389 1 222-23-11 C

SF00000390 2 222-23-11 C

RI87654321 1 AAA-BB-DD C

RI87654321 2 AAA-BB-DD T

CA99999999 2 AAA-BB-EE T

(7 row(s) affected)