Create a join that can (include some) or (include all except some) records

Question

Consider the following two tables:

User
 - UserID
 - UserName

Group
 - GroupID
 - GroupName

The obvious association is that Users will be in Groups. This by itself is a simple many-to-many join situation, so lets add a 3rd table:

UserGroup
 - UserID
 - GroupID

Under this textbook schema, I can easily include a specific User in a specific Group by INSERTing a new record into the UserGroup table. Before going any further I want to point out that I recognize this as the optimal situation in database design.

However, I want to also be able to make Groups that include all Users by default unless they are specifically excluded somehow. Logically, I've broken this down into two "modes" for which a Group must be in one or the other:

• Include Mode: Every User is excluded unless specifically included.
• Exclude Mode: Every User is included unless specifically excluded.

So what is the best way to design such a relationship? You can add columns, add tables, and have fancy join conditions and WHERE constraints. No triggers or s'procs, please.

Answer 1

Since all-inclusiveness and all-exclusiveness is an attribute on groups, the clearest way seems to be to add a column to the Group table specifying whether or not it should include all users, exclude all users, or have default behavior (neither).

Answer 2

Add field to Group table, 'Mode' - 0=Include, 1=Exclude

Then, Users in linking table for an 'Exclusive' Group would be put into a "NOT IN ()" list or "EXCEPT" clause when doing your queries; whereas 'Inclusive' Groups would be queried normally.

See http://msdn.microsoft.com/en-us/library/ms188055.aspx for EXCEPT syntax/usage.

EDIT: oh, somebody posted before me... hopefully this is still helpful!

A SELECT query for an 'Exclusive' Group would go something like this:

SELECT UserID
FROM Users
EXCEPT
SELECT UserID
FROM UserGroup
WHERE GroupID = X

To make it more dynamic, just determine the Group-mode before building the query, then replace 'EXCEPT' with 'INTERSECT' if the Group-mode is 'Inclusive'.

Answer 3

This one had me thinking for a little bit on how to easily write the final query to get a list of groups that a particular user is in, but here it is. Either way, setting an attribute in the Group table is the best way to determine if the group is an "Include" or "Exclude" group. This includes all of the create and insert statements used for testing it.

CREATE TABLE MUser (UserID INT, UserName VARCHAR(64))
GO
CREATE TABLE MGroup (GroupID INT, GroupName VARCHAR(64), GroupTypeID INT)
GO
CREATE TABLE MGroupType (GroupTypeID INT, GroupTypeName VARCHAR(64))
GO
CREATE TABLE MUserGroup (UserID INT, GroupID INT)
GO

INSERT INTO MGroupType VALUES(1, 'Include')
INSERT INTO MGroupType VALUES(2, 'Exclude')

INSERT INTO MGroup VALUES(1, 'Group 1a', 1)
INSERT INTO MGroup VALUES(2, 'Group 1b', 1)
INSERT INTO MGroup VALUES(3, 'Group 2a', 2)
INSERT INTO MGroup VALUES(4, 'Group 2b', 2)

INSERT INTO MUser VALUES (1, 'User1')
--included in 1a, 1b; excluded from 2a, 2b
INSERT INTO MUserGroup VALUES(1, 1)
INSERT INTO MUserGroup VALUES(1, 2)
INSERT INTO MUserGroup VALUES(1, 3)
INSERT INTO MUserGroup VALUES(1, 4)

INSERT INTO MUser VALUES (2, 'User2')
--included in 1a; implicitly included in 2b
INSERT INTO MUserGroup VALUES(2, 1)
INSERT INTO MUserGroup VALUES(2, 3)

INSERT INTO MUser VALUES (3, 'User3')
--implicitly included in 2b
INSERT INTO MUserGroup VALUES(3, 3)

--SELECT ALL GROUPS FOR A PARTICULAR USER
DECLARE @UserID INT
SET @UserID = 3

SELECT g.GroupName
FROM MGroup g WITH(NOLOCK)
LEFT JOIN (
      SELECT *
      FROM MUserGroup WITH(NOLOCK)
      WHERE UserID = @UserID
     ) ug ON g.GroupID = ug.GroupID
WHERE (g.GroupTypeID = 1 AND ug.UserID IS NOT NULL)
OR (g.GroupTypeID = 2 AND ug.UserID IS NULL)

--SELECT ALL USERS FOR A PARTICULAR GRUP
DECLARE @GroupID INT
SET @GroupID = 4

SELECT u.UserName
FROM MUser u WITH(NOLOCK)
JOIN (SELECT * FROM MGroup WITH(NOLOCK) WHERE GroupID = @GroupID AND GroupTypeID = 2) g ON NOT EXISTS (SELECT * FROM MUserGroup ug WITH(NOLOCK) WHERE u.UserID = ug.UserID AND g.GroupID = ug.GroupID)
UNION
SELECT u.UserName
FROM MUser u WITH(NOLOCK)
JOIN MUserGroup ug WITH(NOLOCK) ON u.UserID = ug.UserID
JOIN MGroup g WITH(NOLOCK) ON ug.GroupID = g.GroupID
WHERE g.GroupID = @GroupID
    AND g.GroupTypeID = 1

Answer 4

This is the solution I ended up using. I added JoinMode to the Group table which has valid values of 'IN' and 'EX' for inclusive and exclusive, respectively. Everyone seemed to agree that that's the way to do it. I think this makes the join table's contents rather confusing, but hopefully it's the least bad solution.

In the query I do a CROSS JOIN to create all possible relationships and then drop the unwanted ones in the WHERE constraints based on whether the join table value exists or not.

DECLARE @User TABLE
    (UserID   int
    ,UserName varchar(16))
INSERT INTO @User VALUES (1, 'John')
INSERT INTO @User VALUES (2, 'Jim')
INSERT INTO @User VALUES (3, 'Bob')
INSERT INTO @User VALUES (4, 'George')

DECLARE @Group TABLE
    (GroupID   int
    ,GroupName varchar(16)
    ,JoinMode  char(2))
INSERT INTO @Group VALUES (1, 'Nobody',    'IN')
INSERT INTO @Group VALUES (2, 'Only John', 'IN')
INSERT INTO @Group VALUES (3, 'Jim & Bob', 'IN')
INSERT INTO @Group VALUES (4, 'Not John',  'EX')
INSERT INTO @Group VALUES (5, 'Everybody', 'EX')

DECLARE @UserGroup TABLE
    (UserID   int
    ,GroupID   int)
INSERT INTO @UserGroup VALUES (1, 2) -- Only John
INSERT INTO @UserGroup VALUES (2, 3) -- Jim & Bob
INSERT INTO @UserGroup VALUES (3, 3) -- Jim & Bob
INSERT INTO @UserGroup VALUES (1, 4) -- Not John

SELECT
    g.GroupName
    ,u.UserName
FROM
    (
     @User u
     CROSS JOIN
     @Group g
    )
    LEFT OUTER JOIN
    @UserGroup ug ON (
     u.UserID = ug.UserID
     AND g.GroupID = ug.GroupID
    )
WHERE
    (
     g.JoinMode = 'IN'
     AND ug.UserID IS NOT NULL
    )
    OR (
     g.JoinMode = 'EX'
     AND ug.UserID IS NULL
    )
ORDER BY
    g.GroupID
    ,u.UserID

Let me know if you see any problems with this approach or if you have a better way.

Answer 5

Are you trying to produce a table of all users and groups?

If not, if you just want to know which groups a user is in or which users are in a group, the above would be a very slow approach to a query to get there.

Oh, and there's the question whether this must all be done with one query, or if you can mix in some application code.

To find all the users in a group, my first thought would be to have an application first read the Group record and check the join mode. Then if the join mode is "in", run the conventional query:

select userName
from userGroup ug
join user u using (userId)
where ug.groupId=?

If the join mode is "ex", run:

select userName
from user u
where not exists (select * from userGroup ug where ug.groupId=? and ug.userId=u.userId)

If it must be done with a single query for some reason, perhaps:

select userName
from group g
left join user u on joinMode='in'
  and exists (select * from userGroup ug where ug.groupid=g.groupid and ug.userid=u.userid)
or joinMode='ex'
  and not exists (select * from userGroup ug where ug.groupid=g.groupid and ug.userid=u.userid)
where g.groupid=?

This works but frankly I'm not sure what performance would be like. I did an explain plan in MySQL and it decided to read the entire User table, but then I only had three records in it, maybe with more records it would have made a different plan.