Author: Steven Neiland
Published:

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 (
      userId
      ,groupId
)
SELECT DISTINCT
      u1.userId
      ,g1.groupId
FROM
      dbo.users AS u1
      ,dbo.groups AS g1
WHERE
      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.

What Do You Think?

Reader Comments

Post a Comment

Comment Etiquette:

  • 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 or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.

*
*



Archives Blog Listing

Tag Listing

Learn CF In A Week

Treehouse

 
Fork me on GitHub