title: Get the inverse of a join?
body: <p>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.</p>
  
  <p>I have a view to get all the user information for a group as follows:</p>
  
  <pre><code>SELECT * FROM GroupUsers JOIN Users ON GroupUsers.UserID = Users.UserId
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>SELECT * FROM Users WHERE UserID NOT IN 
      (SELECT UserID FROM GroupUsers WHERE GroupID=@GroupID)
  </code></pre>
  
  <p>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.</p>
  
  <p>I'm not sure how to do this, maybe something with the EXCEPT operator?</p>
  
  <p>UPDATE:</p>
  
  <p>I think this is my solution, unless someone comes up with something better:</p>
  
  <pre><code>SELECT 
     G.GroupId,
     U.*
  FROM
     Groups G
  CROSS JOIN
     Users U
  WHERE
     U.UserId NOT IN 
       (
          SELECT 
             UserId
          FROM
             GroupUsers
          WHERE
             GroupId=G.GroupId
       )
  </code></pre>