Looking for SQL that returns a list of GROUPIDs (from the ACCOUNT_GROUP) table that have no ACCOUNTS that are found on the ACCOUNT_LIST table.
ACCOUNT_GROUP (table)
| GROUPID | ACCOUNT |
|---|---|
| GROUP1 | 111111 |
| GROUP1 | 222222 |
| GROUP1 | 333333 |
| GROUP2 | 222222 |
| GROUP3 | 333333 |
| GROUP4 | 444444 |
ACCOUNT_LIST (table)
| ACCOUNT |
|---|
| 111111 |
| 222222 |
| 444444 |
The result should return only GROUP3 since this is the only GROUPID that does not have at least one account in the ACCOUNT_LIST table.
I have tried a few WHERE NOT EXISTS, not getting the correct results
This returns GROUPIDs that have an Account that is not in the ACCOUNT_LIST table even if it has some that do exist (which is not what I need)
SELECT DISTINCT GROUPID
FROM ACCOUNT_GROUP T1
WHERE NOT EXISTS (
SELECT 1
FROM ACCOUNT_LIST T3
WHERE T1.ACCOUNT = T3.ACCOUNT
)
Answer
You can accomplish that in two steps:
- Determine for each
ACCOUNT_GROUProw whether an entry exists in theACCOUNTtable - Then aggregate by
GROUPIDand only keep groups for which there are no entries in theACCOUNTtable which we can do by summing the total of the calculated valueACCOUNT_EXISTSover all the rows with the sameGROUPID.
SELECT GROUPID
FROM (
SELECT *
, CASE WHEN EXISTS (SELECT 1 FROM ACCOUNT A WHERE A.ACCOUNT = AG.ACCOUNT) THEN 1 ELSE 0 END ACCOUNT_EXISTS
FROM ACCOUNT_GROUP AG
) x
GROUP BY GROUPID
HAVING SUM(ACCOUNT_EXISTS) = 0;