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.

Answer 2

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.

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.

Answer 3

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.

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:

# 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.