To find the Missing rows

[SmithToronto]

Every CEO should have both 'SEYT' and 'ZZZZ' records in Acc_Personal. I want to find any CEO record if anyone is missing 'ZZZZ' record. See table below.

To more clear: if CEO has 'SAYT' in Acc_Personal but not 'ZZZZ' then this record should show in the desired output. This condition should be in the query to grab the correct result.

If CEO has both 'SAYT' and 'ZZZZ' in Acc_Personal then this CEO number should not be in the desired output. This condition should be in the query to grab the correct result.

CREATE TABLE DBO.TMP (CEO VARCHAR(20), Acc_Personal VARCHAR(20), AMT INT, DATEA DATETIME, STAT VARCHAR(1))

INSERT INTO DBO.TMP VALUES ('10001','SEYT',78, '2024-04-09','N'

INSERT INTO DBO.TMP VALUES ('10001','ZZZZ',12, '2024-03-09','N'

INSERT INTO DBO.TMP VALUES ('10002','SEYT',45, '2024-06-02','N'

INSERT INTO DBO.TMP VALUES ('10002','ZZZZ',55, '2024-07-07','D'

INSERT INTO DBO.TMP VALUES ('10003','SEYT',76, '2024-08-09','N'

INSERT INTO DBO.TMP VALUES ('10004','SEYT',45, '2024-04-02','C'

INSERT INTO DBO.TMP VALUES ('10004','ZZZZ',21, '2024-07-09','N'

INSERT INTO DBO.TMP VALUES ('10005','SEYT',57, '2024-04-01','N'

INSERT INTO DBO.TMP VALUES ('10006','ZZZZ',59, '2024-04-01','B'

INSERT INTO DBO.TMP VALUES ('10006','SEYT',47, '2024-02-01','A'

INSERT INTO DBO.TMP VALUES ('10007','SEYT',59, '2024-04-09','N'

INSERT INTO DBO.TMP VALUES ('10010','AAAA',59, '2024-04-01','B'

INSERT INTO DBO.TMP VALUES ('10010','ZZZZ',47, '2024-02-01','A'

Note: CEO-10010 should not be in the result because this CEO has no record of SEYT in Acc_personal.

Desired Output

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

CEO    Acc_personal AMT  DATEA  STAT

1003

1005

1007

[#2]

@SmithToronto 

Hi, Nadeem.

Your expected results don't quite match your sample data, where "10006" has two entries for "SEYT" rather than one each for "SEYT" and "ZZZZ", meaning "10006" should feature in your results.

Setting that aside, here's an example.

T-SQL

SELECT
	[ceos].[ceo]
FROM
	(SELECT DISTINCT [ceo] FROM [tmp]) AS [ceos]
	LEFT OUTER JOIN [tmp] AS [t] ON
		[ceos].[ceo] = [t].[ceo]
		AND [t].[acc_personal] = 'ZZZZ'
WHERE
	[t].[ceo] IS NULL;

Output

Cheers,

Lain

[#3]

Hi, I have corrected the data in original request and more clarified in original request. But query is not correct. There is no condition for "SEYT" in the query that's why it will grab every CEO record if CEO has no 'ZZZZ' in Acc_Personal. 

I said that "Every CEO should have both 'SEYT' and 'ZZZZ' records in Acc_Personal. I want to find any CEO record if anyone is missing 'ZZZZ' record. See table below.".

Meaning if CEO has 'SAYT' in Acc_Personal but not 'ZZZZ' then this record should show in the desired output. This condition should be in the query to grab the correct result.

If CEO has both  'SAYT' and  'ZZZZ' in Acc_Personal then this CEO number should not be in the desired output. This condition should be in the query to grab the correct result.

Thanks,

Desired Output:

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

CEO Acc_personal AMT DATEA STAT
1003
1005
1007

[#4 · Accepted solution]

@SmithToronto 

Having accurate data and an in-depth description certainly makes things easier.

T-SQL

SELECT
	[ceos].[ceo]
FROM
	(SELECT DISTINCT [ceo] FROM [tmp]) AS [ceos]
	LEFT OUTER JOIN [tmp] AS [t1] ON
		[ceos].[ceo] = [t1].[ceo]
		AND [t1].[acc_personal] = 'SEYT'
	LEFT OUTER JOIN [tmp] AS [t2] ON
		[ceos].[ceo] = [t2].[ceo]
		AND [t2].[acc_personal] = 'ZZZZ'
WHERE
	[t2].[ceo] IS NULL;

Output

Cheers,

Lain

[#5]

Thanks so much