| I can’t think of a good enough examples on this topic. However, I’ll try to explain. Let’s say we have 3 tables. Products, Sizes and Items. The Products table holds your products for example - T-Shirt, Shoes and so on. Sizes table holds the sizes for those items - Large, Medium and Small. The Items table holds a combination of valid items - like : Shoes Large, Shoes Small, Shoes Medium and so on. Let’s say that you have 3 records in the Products table and 3 records in the Sizes table. You need to create all combinations between them (Shoes S, Shoes M, Shoes L …). So in C# would look like this: foreach(Record recordProduct in Products) { foreach(Record recordSize in Sizes) { // Check if the compound record (recordProduct.ProductID, |
// add it if it doesn't.
}
}
I think most of the developers will use a cursor to achieve this. But I didn’t. I don’t like cursors very much so I tried to do it my own way.
Here are the create scripts for those tables first:
Products:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Products](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
Sizes:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Sizes](
[SizeID] [int] IDENTITY(1,1) NOT NULL,
[SizeName] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
And finally, Items:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Items](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[SizeID] [int] NOT NULL,
[ProductID] [int] NOT NULL
) ON [PRIMARY]
We want to add all the combinations in the Items table. To make it a bit more complex we would imagine that some of the items will be already generated so prior adding a combination we would like to check if it does not exist.
Here is my script to achieve this:
INSERT INTO Items
SELECT S.SizeID, P.ProductID FROM Sizes S, Products P
WHERE (SELECT ItemID FROM Items WHERE SizeID=S.SizeID AND ProductID=P.ProductID) IS NULL
3 lines of code…
Run it. It should affect some rows when starting it for first time. Run it again, without adding new records to Products or Sizes. It should say: “0 rows affected.”. Now add a product or a size or both. Run again. It will generate the new combinations.
Seems to be fine.
Considerations:
First of all I don’t think you will have to update such table on a daily basis. You may have a table to connect two tables but typically you will not need to generate all possible combinations. You may want to do this if you are updating some database on staging / production server for example.
Second - I am not quite if this approach is faster than the cursors / temp tables or any other approaches you may come up with, so use your head, don’t trust me. I am proven to make fundamental mistakes (see Check if a character is lower case post for reference ;)).
Third - I think you can use this to add another nested loop, so you can have nested loop in a nested loop in a nested loop. Or, with other words, to generate all possible combination between 3 or 4 or even more tables. I can not think of something to stop you doing so.
And last but not least - if you really need to access a combination of two tables, you may consider using view to select them. This way you will not have a physical table to take care of. It’s up to you.
I really love critics, positive or negative ;)
