Get the inverse of a join?

Question

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) 

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.

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

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      ) 

Answer 1

You can use a left outer join to 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:

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 only exclude the users in that particular group. Every other user will be returned. This is because the groupid condition was done in the join clause, which limits the rows joined, not returned, which is what the where clause does.