Having trouble writing a SQL Sub-Query

Question

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.

Your query doesn't make sense. If you `join` to a table you only get those that have values. If you want to get those users without transactions you can't use `join`. — Shmiel, Jun 15 '23 at 13:23

score 2 · Accepted Answer · answered Jun 15 '23 at 13:34

2

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.

answered Jun 15 '23 at 13:34

Charlieface

52,284
6
19
43

That did it! I actually meant to have users WITH transactions but no Variables, but I worded the question incorrectly. Thanks to your example, it was as simple as turning first Where to "WHERE EXISTS" and I appreciate the explanation! Thank you! – jmath412 Jun 15 '23 at 13:56
I wouldn't say _incorrect_ results, rather _different_, or perhaps _surprising_. – jarlh Jun 15 '23 at 14:03
Incorrect as in: not what is expected for the query spec, and incorrect for most people's expectations. – Charlieface Jun 15 '23 at 15:46

Having trouble writing a SQL Sub-Query

1 Answers1