得到联接的逆向? [英] Get the inverse of a join?

问题描述

我正在使用SQL Server2005.我有三个表-用户，组和组用户. GroupUsers包含用于多对多关系的两个PK.

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.

我有如下观点来获取组的所有用户信息:

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)

但是，我不想指定组，我想知道如何将其变成一个视图，该视图将GroupID，UsersID和所有用户信息连接在一起，但仅适用于未连接的用户.

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.

我不确定如何执行此操作，也许不知道使用EXCEPT运算符是什么?

I'm not sure how to do this, maybe something with the EXCEPT operator?

更新:

我认为这是我的解决方案，除非有人提出更好的建议:

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
     )

推荐答案

如果我正确理解，您将不得不对用户&分组并减少从GroupUsers得出的结果.

If I understand it correctly, you will have to do a cartersian result of users & groups and reduce the result derived from 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.

笛卡尔结果将为您提供用户*组.您将不得不从中减去GroupUsers.抱歉，我还没有准备好使用SQL&目前无法尝试.

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.

这篇关于得到联接的逆向?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！