SQL 获取联接的逆？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1247361/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get the inverse of a join?
提问by Joel
I am using SQL Server 2005. I have three tables - Users, Groups, and GroupUsers. GroupUsers contains the two PKs for a many-to-many relationship.
我使用的是 SQL Server 2005。我有三个表 - 用户、组和组用户。GroupUsers 包含多对多关系的两个 PK。
I have a view to get all the user information for a group as follows:
我有一个视图来获取一个组的所有用户信息，如下所示：
SELECT * FROM GroupUsers JOIN Users ON GroupUsers.UserID = Users.UserId
I want to create the inverse of this view - I want a list of all of the users NOT attached to a specific group. The following query would accomplish this:
我想创建此视图的反面 - 我想要一个未附加到特定组的所有用户的列表。以下查询将完成此操作：
SELECT * FROM Users WHERE UserID NOT IN
(SELECT UserID FROM GroupUsers WHERE GroupID=@GroupID)
However I don't want to have to specify the group, I want to know how to turn this into a view that joins the GroupID and then the UsersID and all the user info, but only for non-attached users.
但是我不想指定组，我想知道如何将其转换为加入 GroupID 然后是 UsersID 和所有用户信息的视图，但仅适用于非附加用户。
I'm not sure how to do this, maybe something with the EXCEPT operator?
我不知道如何做到这一点，也许与 EXCEPT 运算符有关？
UPDATE:
更新：
I think this is my solution, unless someone comes up with something better:
我认为这是我的解决方案，除非有人想出更好的办法：
SELECT
G.GroupId,
U.*
FROM
Groups G
CROSS JOIN
Users U
WHERE
U.UserId NOT IN
(
SELECT
UserId
FROM
GroupUsers
WHERE
GroupId=G.GroupId
)
采纳答案by shahkalpesh
If I understand it correctly, you will have to do a cartersian result of users & groups and reduce the result derived from GroupUsers.
如果我理解正确，您将不得不对用户和组进行 Cartersian 结果并减少从 GroupUsers 派生的结果。
That will give you records of users which do not have any groups attached to it.
I apologize if I didn't understand the question correctly.
这将为您提供没有附加任何组的用户记录。
如果我没有正确理解问题，我深表歉意。
EDIT: Cartesian result will give you users * groups. You will have to subtract GroupUsers from it. I am sorry, I do not have SQL ready for it & can't try it out at this point.
编辑：笛卡尔结果将为您提供用户 * 组。您必须从中减去 GroupUsers。抱歉，我还没有准备好 SQL，目前无法尝试。
回答by Eric
You can use a left outer jointo grab all of the users, then, blow away any user where there's a group attached. The following query will give you just the list of users where there's no group to be had:
您可以使用 aleft outer join来获取所有用户，然后删除任何附加了组的用户。以下查询将为您提供没有组的用户列表：
select
u.*
from
users u
left outer join groupusers g on
u.userid = g.userid
where
g.userid is null
If you want to find all users not in a particular group:
如果要查找不在特定组中的所有用户：
select
u.*
from
users u
left outer join groupusers g on
u.userid = g.userid
and g.groupid = @GroupID
where
g.userid is null
This will onlyexclude the users in that particular group. Every other user will be returned. This is because the groupidcondition was done in the joinclause, which limits the rows joined, not returned, which is what the whereclause does.
这只会排除该特定组中的用户。其他所有用户都将被退回。这是因为groupid条件是在join子句中完成的，它限制了连接的行，而不是返回的行，这就是where子句所做的。
回答by daicoden
I couldn't figure out how to get previous version to work via active record, got some of the way there but had to write an SQL in statement. I believe this also accomplishes the same thing.
我不知道如何通过活动记录让以前的版本工作，得到了一些方法，但不得不在语句中编写一个 SQL。我相信这也完成了同样的事情。
SELECT * FROM Users WHERE UserID NOT IN
(SELECT U.UserID FROM GroupUsers AS G, Users as U WHERE G.UserID <> U.UserID)
Couldn't test however this query in rails worked just fine:
无法测试，但是 Rails 中的这个查询工作得很好：
# Gets Pre-Clients. Has client information but no project attached
Contact.joins(:client).includes(:projects => :primary_contact).
where("contacts.id NOT IN (select contacts.id from contacts,
projects where projects.primary_contact_id = contacts.id)")
Thanks for the post got me 90% of the way there.
感谢您的帖子让我完成了 90% 的工作。
