SQL Sets (intersect, Union, And Not)

Question

I have what appears to be a simple query, but is alluding my boolean challenged mind (not enough java (the liquid kind) today).

Three tables:

Users = (UserID, Username, Enabled, LoggedIn, SessionID, Email, SettingsTableVersion, FullName, Initials, UserData, InitialStatusID)
Groups = (GroupID, Groupname, Description, AutoAdd)
GroupMembers = (GroupID, UserID, ProjectID, IsMember)

I have a bunch of users and a dozen or so groups. I have a World group that has every User in it. I have a Terminated Users Group that has just 4 users in it.

What I want is a query that looks at World (everyone is in it) and takes out the Terminated User group users names. This yields me all active users! Blimey if this isn't causing me to pull my hair out. I would surmise its essentially World minus the intersection of World and Terminated Users. No luck thus far. SQL Server 2012.

TIA

I just dont know why you have a World Group? That looks like is the same as the whole `Users` table, isnt? — Juan Carlos Oropeza, Sep 20 '17 at 19:56
Possible duplicate of [How do I 'subtract' sql tables?](https://stackoverflow.com/questions/1373833/how-do-i-subtract-sql-tables) — philipxy, Sep 22 '17 at 11:39

score 3 · Answer 1 · edited Sep 20 '17 at 19:54

3

A nested query?

Select * 
from users 
where userid not in ( select userid 
                      from groupmembers 
                      where groupid=[terminated])

Or is that too slow?

edited Sep 20 '17 at 19:54

Juan Carlos Oropeza

47,252
12
78
118

answered Sep 20 '17 at 19:54

Henrik Clausen

679
6
16

Doesn't seem slow - and its giving me all the right answers with one exception. I may have to check for deletion of user to filter it out. Looking good at this point. Thanks. – CodeWriter Sep 20 '17 at 19:59

xQbert · Answer 2 · 2017-09-20T19:59:53.430

If I can assume

The groupnames are 'world' and 'terminatedusers'
duplicates do not exist in group members (groupID and userID are a Unique index)
you really mean World is ALL users an a terminated user coudnl't exist w/o being in the world group.

.

SELECT U.userID
FROM USERS U
INNER JOIN GROUPMEMBERS GM
  on U.UserID = GM.UserID
INNER JOIN GROUPS G
 on G.GroupID = GM.GroupID
WHERE GroupName in ('World', 'TerminatedUsers')
GROUP BY U.UserID 
HAVING count(GM.GroupID) = 1

This basically find all the groups each user is in for the two groups. then only return those who are in just 1 group.

You could join to two defined sets as well:

SELECT U.userID
FROM Users U
INNER JOIN GroupMembers GMWorld
  on U.userID = GMWorld.userID
 and GMWorld.GroupID = [ID for world]
LEFT JOIN GroupMembers GMTerminated
  on U.userID = GMTerminated.UserID
 and GMTerminated.GroupID = [ID for terminated]
WHERE GMTerminated.userID is null

If you don't want to use the ID's you could left join twice to Group for GMterminated and GMWorld to use the names.

The 2nd query basically joins users to a worldset and a terminated set we keep all users in world except those having a record in the terminated set.

score 0 · Answer 3 · answered Sep 20 '17 at 20:06

0

This one did the trick! Issue was I had one user that was deleted (flips the Enabled bit) and then re-added so they appeared in the list until I added the Enabled = 1. Thanks xQbert... again! Awesome stuff.

SELECt fullname
    FROM Users U
    LEFT JOIN GroupMembers GMWorld
      on U.userID = GMWorld.userID
     and GMWorld.GroupID = 3
    LEFT JOIN GroupMembers GMTerminated
      on U.userID = GMTerminated.UserID
     and GMTerminated.GroupID = 14
    WHERE GMTerminated.userID is null and Enabled = 1

answered Sep 20 '17 at 20:06

CodeWriter

71
1
13

If you have users not in world they would still show up. The left join to groupmembers GMworld is actually unneeded. I switched it to an INNER join so that on the off chance you had users not in the world group they were excluded from the results. Test it out. Add a user w/o them being in group 3. They are kept when using left join on gmworld, omitted when you use an inner join on gmworld. – xQbert Sep 20 '17 at 20:16
I think @HenrikClausen should work as well provided you handle the enabled in it. it could be joined to groupmembers for groupID = 3 to ensure only users in the world group are returned. – xQbert Sep 20 '17 at 20:23

SQL Sets (intersect, Union, And Not)

3 Answers3