Insert new relation 'B' for all data where relation 'A' does not exist?

Question

Ok 3 tables, Users, Groups, and UserGroups.

The important columns are Users.UserID, Groups.GroupID, UserGroups.UserID, and UserGroups.GroupID.

I have a group we'll call group 'A', there are a bunch of Users in this group because there are many rows of UserGroups where the GroupID is the GroupID of group 'A'.

Now for all the users who do not have this UserGroup association I want to insert a new UserGroup association for group 'B'.

So, put all users not in group 'A' into group 'B', a user is in a group when there is a UserGroup with that UserID and that GroupID in existence.

How can I write a query that will insert a UserGroup of UserID and GroupID of group 'B' for all the users that do not already have a UserGroup with the GroupID of group 'A'?

Also, how can I add additional GroupIDs to the exclusion? Meaning if there was a group 'C' as well and I only wanted to add the users to group 'B' who are in neither 'A' nor 'C'?

Answer 1

Does this work for you?

INSERT INTO UserGroups (GroupID, UserID)
SELECT g.GroupID, u.UserID
FROM Users u
CROSS JOIN Groups g
WHERE g.Group = 'B'
    AND u.UserID NOT IN
        (SELECT u2.UserID
        FROM Users u2
        JOIN UserGroups ug2 on u2.UserID = ug2.UserID
        JOIN Groups g2 on g2.GroupID = ug2.Group_ID
        WHERE g2.Group in ('A')
        )

When excluding additional groups, you would change WHERE g2.Group in ('A') to WHERE g2.Group in ('A', 'C').