Find list of GROUPIDs that have ZERO AccountIDs on another table

Question

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
)

Are you familiar with "Accepting" an answer? Please consider it as it makes it easier for others to see which solution worked for you. — Dale K
– Dale K, Commented Jun 2 at 2:10

Tim Biegeleisen · Accepted Answer · 2025-06-01 08:39:07Z

4

A simple left anti-join with aggregation should work here:

SELECT AG.GROUPID
FROM ACCOUNT_GROUP AG
LEFT JOIN ACCOUNT_LIST AL
    ON AL.ACCOUNT = AG.ACCOUNT
GROUP BY AG.GROUPID
HAVING COUNT(AL.ACCOUNT) = 0;

edited Jun 1 at 8:39

answered Jun 1 at 3:58

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Don Neary Jun 2 at 2:13

This SQL works perfect in AQT. It also "works" in SPUFI, but also lists about 8 lines of "warnings" between each row returned back.

Dale K Jun 2 at 6:58

Are the warnings relevant? Not all warnings are actually a problem - that's why they are a warning

Dale K · Accepted Answer · 2025-06-01 03:12:57Z

3

You can accomplish that in two steps:

Determine for each ACCOUNT_GROUP row whether an entry exists in the ACCOUNT table
Then aggregate by GROUPID and only keep groups for which there are no entries in the ACCOUNT table which we can do by summing the total of the calculated value ACCOUNT_EXISTS over all the rows with the same GROUPID.

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;

db<>fiddle

answered Jun 1 at 3:12

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

2 Comments

Dale K Jun 1 at 3:16

FYI: For future questions, what people were asking for in staging group is a minimal reproducible example similar to what is contained in the DBFiddle here i.e. create table, insert into table, your current attempt. The reason being is that it makes it much easier for us to assist you.

Tim Biegeleisen Jun 1 at 4:33

You don't need a formal subquery, q.v. my answer

ValNik · Accepted Answer · 2025-06-01 19:04:05Z

2

Query as an operation on sets

select groupid 
from account_group

except

select groupid
from account_group g
inner join account_list l on l.account=g.account

(Perhaps this can be called a division remainder.) fiddle

edited Jun 1 at 19:04

answered Jun 1 at 18:34

ValNik

7,8381 gold badge8 silver badges18 bronze badges

Comments

Dale K · Accepted Answer · 2025-06-01 22:16:19Z

2

For the sake of completeness: you could adapt your NOT EXISTS approach,
by looking up for "rows that have no sibling (of same GROUPID) matching ACCOUNT_LIST":

SELECT DISTINCT GROUPID
FROM ACCOUNT_GROUP T1
WHERE NOT EXISTS (
    SELECT 1
    FROM ACCOUNT_LIST T3
    JOIN ACCOUNT_GROUP BRO ON BRO.ACCOUNT = T3.ACCOUNT
    WHERE T1.GROUPID = BRO.GROUPID
)

edited Jun 1 at 22:16

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Jun 1 at 15:23

Guillaume Outters

7,4961 gold badge22 silver badges28 bronze badges

2 Comments

Guillaume Outters Jun 1 at 15:44

… But that's just for the challenge, and because the more tools you think of, the most chances you give yourself to find the optimal one. But in today's case, I too would have opted for one of the COUNT() = 0 / SUM() = 0 solutions.

Dale K Jun 2 at 3:49

@DonNeary stackoverflow.com/help/someone-answers

Collectives™ on Stack Overflow

Find list of GROUPIDs that have ZERO AccountIDs on another table

4 Answers 4

2 Comments

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related