Populate a many-to-many relationship table with all possible combinations

Author: Steven Neiland

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

Sometimes when you are working with databases it is faster to add everything and remove selected data than it is to just add in what you need. For example I recently needed to create a new many-to-many table which contained all possible combinations of the two parent tables excluding 3 cases.

Example: Add all users to all groups excluding admin

Take this contrived example. Say we have a users table and a groups table with PK's 'userId' and 'groupId' respectively and we want to form a many-to-many table named userGroups with all possible combinations of userId and groupId excluding the admin groupId of 1.

You will note the use of the NOT EXISTS sub query to exclude any records that may already exist in the userGroups table preventing any PK violations. While not strictly necessary if you are starting with an empty table it can be useful if you already have some data in there.

INSERT INTO dbo.userGroups (
      dbo.users AS u1
      ,dbo.groups AS g1
      NOT EXISTS (
            SELECT *
            FROM dbo.userGroups AS ug
            WHERE ug.userId = u1.userId
            AND ug.groupId = g1.groupId
      AND g1.groupId <> 1

Now I know this is a contrived example, but what it demonstrates is the key point that you can populate any table with data combined from two or more other tables.

Reader Comments

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain advertisments or appear to be created for the purpose of link building, will not be published.

Archives Blog Listing