CODEWITHSUNDEEP
sqlexcept
Don Neary
Don Neary

Reputation: 11

SQL to find where not exists on one table

I want to find the list of any COMPANYID/USERID combos on the USERS table that does NOT have at least 1 ACCOUNT found on the ACCOUNT table. In the example below, ABC/USER3 is the only combo that fits this. The rest of them have at least 1 ACCOUNT that is on the Account table.

TABLE: USERS

COMPANYID USERID ACCOUNT
ABC USER1 111
ABC USER1 222
ABC USER1 333
ABC USER2 222
ABC USER2 444
ABC USER2 888
ABC USER3 333
ABC USER3 666

TABLE: ACCOUNT

ACCOUNT
111
222
444
555
777

Upvotes: 0

Views: 35

Answers (2)

Stu
Stu

Reputation: 32609

You're looking for where something does not exist in an exclusive list of those that do exist; Using the exists operator will likely be the most efficient method.

select distinct CompanyId, UserId
from users u where not exists (
    select userid
    from users u2
    where exists (select * from account a where a.account = u2.account)
    and u2.userid=u.userid
)

Upvotes: 0

LukStorms
LukStorms

Reputation: 29657

With a left join, those will be having 0 matching accounts.

SELECT USR.COMPANYID, USR.USERID
FROM USERS USR
LEFT JOIN ACCOUNT ACC ON ACC.ACCOUNT = USR.ACCOUNT
GROUP BY USR.COMPANYID, USR.USERID
HAVING COUNT(ACC.ACCOUNT) = 0

Upvotes: 0

Related Questions