Check which value not present with set4 but present in any of the other set in sql

1

Respected Techie,

may anyone please help me on this critical scenario.

I am trying to PULL records when YEAR,MRN and DOCTOR_CODE for DISEASE_TYPE (MALARIA OR TYPHOID OR GLUCOMA OR ORAL) are present
but same YEAR,MRN and DOCTOR_CODE not present with CANCER

DDL:

DECLARE @APP TABLE
(
DISEASE_TYPE VARCHAR (50),
year VARCHAR (50),
MRN VARCHAR (50),
DOCTOR_CODE VARCHAR (50)

)

INSERT @APP

SELECT 'MALARIA', '1966', '47', '21448' UNION ALL
SELECT 'MALARIA', '1966', '54', '688' UNION ALL
SELECT 'MALARIA', '1964', '54', '688' UNION ALL
SELECT 'MALARIA', '1970', '47', '21448' UNION ALL -- NOT AVAILABLE WITH DISEASE_TYPE CANCER SO BE IN OUTPUT
SELECT 'MALARIA', '1979', '40', '308' UNION ALL
SELECT 'TYPHOID', '1979', '40', '308' UNION ALL
SELECT 'TYPHOID', '1979', '40', '432' UNION ALL
SELECT 'TYPHOID', '1979', '54', '683' UNION ALL -- NOT AVAILABLE WITH DISEASE_TYPE CANCER SO BE IN OUTPUT
SELECT 'GLUCOMA', '1979', '40', '308' UNION ALL
SELECT 'GLUCOMA', '1979', '40', '432' UNION ALL
SELECT 'GLUCOMA', '1979', '54', '683' UNION ALL -- NOT AVAILABLE WITH DISEASE_TYPE CANCER SO BE IN OUTPUT
SELECT 'ORAL', '1964', '54', '688' UNION ALL
SELECT 'CANCER', '1966', '47', '21448' UNION ALL
SELECT 'CANCER', '1966', '54', '688' UNION ALL
SELECT 'CANCER', '1979', '40', '308' UNION ALL
SELECT 'CANCER', '1979', '40', '432' UNION ALL
SELECT 'CANCER', '1979', '40', '308' UNION ALL
SELECT 'CANCER', '1979', '40', '432' UNION ALL
SELECT 'CANCER', '1964', '54', '688'

Expected Output
'MALARIA', '1970', '47', '21448'
'TYPHOID', '1979', '54', '683'
'GLUCOMA', '1979', '54', '683'

Thank You Very Much

2 
SELECT * FROM @APP a
WHERE
	a.DISEASE_TYPE IN ('MALARIA','TYPHOID','GLUCOMA','ORAL')
	AND NOT EXISTS
	(
		SELECT * FROM @APP b
		WHERE
			a.year = b.year
			AND a.mrn = b.MRN
			AND a.DOCTOR_CODE = b.DOCTOR_CODE
			AND b.DISEASE_TYPE = 'CANCER'
	);
1 Like