英文:
Having trouble writing a SQL Sub-Query
问题
让我展示一下表之间的关系以及我想要找到的内容：
<br>**Users** - 与 **AccountManager** 之间是一对多关系
<br>**AccountManager** - 与 **Accounts** 之间是一对多关系
<br>**Accounts** - 与 **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
编辑：感谢 @Shmiel 指出上面对 `Transactions` 的连接是不正确的。幸运的是，接受的答案解决了这个问题。
**Users** 与 **Variables** 之间是一对多关系
我正在尝试找到没有 `Transactions` **且** 没有 `Variables` 的 `Users`。
我正在使用 SSMS，并编写了前四个表的适当连接，并在末尾使用 `COUNT`，但我不确定如何获取那些没有 `Transactions` 的 `Users`，然后查询他们是否也没有 `Variables`，因为这是与 `Users` 的另一个连接。我相信这只是一个子查询，我漏掉了。
我愿意听取任何建议，希望我只是过于考虑了这个问题。
英文:
Let me show the relationships of the tables and what I'm trying to find:
<br>Users - one to many with AccountManager
<br>AccountsManager - one to many with Accounts
<br>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.
答案1
得分: 2
你需要使用反连接，在SQL中可以使用 NOT EXISTS 完成。
SELECT u.*
FROM Users u
WHERE NOT EXISTS (SELECT 1
FROM AccountManager am
JOIN Accounts a ON a.AccountManagerId = am.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
);
不要诱惑使用 NOT IN，它可能导致不正确的结果。
你也可以使用 LEFT JOIN ... IS NULL 结构，但查询可能不太明显，编译器通常难以推理。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论