How to list values in a column based on two further columns

Transact-SQL
1 
CommonNumber	SCRPatientID	SCRCareID	SCRCareIDInRMSDB
000453	        45500	        51046	    172563
000453	        45500	        172563	    172563
000453	        45500	        180186	    172563
000705	        135549	        205199	    215127
000705	        135549	        207728	    215127
000705	        135549	        215127	    215127
001861	        149485	        231638	    231638
002692	        123250	        180879	    180879
004383	        157837	        250948	    291478
004383	        157837	        280652	    291478
004383	        157837	        291478	    291478
004688          94714           128091      160354
004688          94714           155621      160354
004688          94714           173582      160354

In the above table what script would select the values in CommonNumber that do not have at least one instance of SCRCareIDInRMSDB in the SCRCareID column. The only affected record in this sample table is CommonNumber 004688 as it does not have a matching value of 160354 in the SCRCareID column.

2 
SELECT DISTINCT T1.CommonNumber
FROM YourTable T1
WHERE NOT EXISTS
(
	SELECT 1
	FROM YourTable T2
	WHERE T2.CommonNumber = T1.CommonNumber
		AND T1.SCRCareIDInRMSDB = T2.SCRCareID
);
1 Like