Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-11-01 : 08:29:12
|
| Ken writes "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?" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-01 : 08:32:37
|
| Yep. First thing though, drop the uniqueID column from both of those tables. You do not need it, and you didn't make it the primary key anyway. Use these structures instead:CREATE TABLE dbo.SiteCategories(SiteID int NOT NULL, CategoryID int NOT NULL, CONSTRAINT PK_SiteCategories PRIMARY KEY(SiteID, CategoryID))CREATE TABLE dbo.OrderCategories(OrderID int NOT NULL, CategoryID int NOT NULL, CONSTRAINT PK_OrderCategories PRIMARY KEY(SiteID, CategoryID))Once that's done and you've populated the data, the following should do the trick:SELECT * FROM Orders OWHERE EXISTS(SELECT * FROM OrderCategories OC WHERE OC.OrderID=O.OrderID)AND EXISTS(SELECT * FROM SiteCategories SC WHERE OC.CategoryID=SC.CategoryID)I assume you're trying to match only one SiteID, but I don't know if you're passing that ID in, or joining it to another table. Will need some more info on your other tables to complete this, but it should get you started. |
 |
|
|
|
|
|
|
|
Comparing Multiple Tables