Q

Joining two many-to-many relationship tables

I am building a database that has a table of sites and a table of orders. Each site can have multiple categories and each order can have multiple categories selected from the master list of categories. To store the categories that have been selected for each site, I have a table of site categories which a structure something like this:

 TABLE [dbo].[SiteCategories] [UniqueID] [int] IDENTITY (1, 1) NOT NULL , [SiteID] [int] NULL , [CategoryID] [int] NULL

To store the categories that have been selected for each order, I have a table of order categories which a structure something like this:

 [dbo].[OrderCategories] [UniqueID] [int] IDENTITY (1, 1) NOT NULL , [OrderID] [int] NULL , [CategoryID] [int] NULL

What I want to do is select all orders that have any CategoryID in the OrderCategories table that matches any CategoryID in the SiteCategories table for a particular SiteID. Can you suggest SQL that will do that?

A simple inner join should do it:

 select distinct OrderID from OrderCategories inner join SiteCategories on OrderCategories.CategoryID = SiteCategories.CategoryID and SiteCategories.SiteID = 937

The keyword DISTINCT is required so that you get each order only once, even if it has several categories in common with the specified site.

Note that you don't really need the surrogate key UniqueID in either table. In other words, your tables should be declared as follows:

 create table SiteCategories ( SiteID integer not null , CategoryID integer not null , primary key (SiteID,CategoryID) , foreign key (SiteID) references Sites(ID) , foreign key (CategoryID) references Categories(ID) ) create table OrderCategories ( OrderID integer not null , CategoryID integer not null , primary key (OrderID,CategoryID) , foreign key (OrderID) references Orders(ID) , foreign key (CategoryID) references Categories(ID) )

The problem with your existing design with the IDENTITY columns is that you haven't declared a unique constraint on the composite of the other two columns in each table. This opens the door to the possibility of duplicates. By declaring the composites as primary key, you not only get uniqueness, but you save space as well.

Note that the columns have to be NOT NULL to participate in the primary key. NULL did not make sense anyway, because it would never come up in a real row where you're relating a site to a category or an order to a category.

This was first published in November 2004

Dig deeper on SQL Server Database Modeling and Design

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close