Having trouble writing a SQL Sub-Query

June 15, 2023

Let me show the relationships of the tables and what I’m trying to find:

Users – one to many with AccountManager

AccountsManager – one to many with Accounts

Accounts – one to many with Transactions

Select * from Users U
JOIN AccountManager am on am.AccountManager Id = u.AccountManagerId
JOIN Accounts a on a.AccountManagerId = AccountManager.Id
JOIN Transactions t on t.AccountId = a.Id
WHERE COUNT(t.Id) = 0

Edit: Thanks to @Shmiel for pointing out the above join on Transactions is incorrect. Thankfully, the accepted answer clears that up.

Users one to many with Variables

I’m trying to find Users who have no Transactions AND no Variables.
I’m using SSMS and wrote the appropriate joins for the first four tables using COUNT at the end, but I’m not sure how to get those same Users who have no Transactions and then query if they also have no Variables as that’s another join on Users. I believe it’s just a subquery that I’m missing.

I’m open to any suggestions and hoping I’m just overthinking this one.

>Solution :

You need to use an anti-join, which in SQL is done with a NOT EXISTS.

SELECT u.*
FROM Users u
WHERE NOT EXISTS (SELECT 1
    FROM AccountManager am
    JOIN Accounts a on a.AccountManagerId = AccountManager.Id
    JOIN Transactions t on t.AccountId = a.Id
    WHERE am.AccountManagerId = u.AccountManagerId
)
  AND NOT EXISTS (SELECT 1
    FROM Variables v
    WHERE v.UserId = u.Id
);

Don’t be tempted to use NOT IN, it can cause incorrect results.

You can also use a LEFT JOIN ... IS NULL construct, but the query can be non-obvious, and the compiler often finds it hard to reason about.