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.