Problem with a view (not a simple one)

Hi,

I have a table that is something like this:

Products
--
idProduct as int,
idPart1 as int,
idPart2 as int,
idPart3 as int

then other table, which is something like:

Parts:
-
idPart as int,
partName as VarChar(30)

Now, some products only take one part, but others will take as many as 3 parts. Since it's a relatively small number I thought it would be better to put 3 different idParts on the product table and have two of them allow nulls, instead of creating a Master / Detail kind of thing. My question is, how do I create a view with something like:

v_Products
--
idProduct as int,
namePart1 as VarChar(30),
namePart2 as VarChar(30),
namePart3 as VarChar(30)

I'm using SQL Server 2005 (Express Edition, but the SQL is the same so...). I'm using this code:

SELECT dbo.products.idProduct, dbo.parts1.partName, dbo.parts2.partName, dbo.parts3.partName
FROM dbo.products INNER JOIN dbo.parts AS parts1 ON dbo.products.idPart1=parts1.idPart INNER JOIN(...)

Help? :s

Hi,

I would suggest you not to use the inline stored child data. Store the information about the parts in a parent-child relation rather than in the table itself. Querying will be much easier for you in the future.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Storing data in this format is just asking for trouble in the future. Invariably, you will end up in a situation where you have a product with 4 parts, at which point you need to add extra columns to the table.

However, if you've got this structure already and there's nothing you can do about it, then you need to create your view as follows (this also demonstrates the problems with using this style of database schema)

SELECT p.idProduct, pt1.partName as namePart1, pt2.partName as namePart2, pt3.partName as namePart3

FROM dbo.products p

INNER JOIN dbo.parts pt1

ON p.idPart1 = pt1.idPart

LEFT JOIN dbo.parts pt2

ON p.idPart2 = pt2.idPart

LEFT JOIN dbo.parts pt3

ON p.idPart3 = pt3.idPart

You need the LEFT JOINs to get the 2nd and 3rd parts because those columns may contain null values.

Other problems with this style of table design include the fact that if you are looking for products that use a specific part, you have to query across all three part columns, rather than just having a single part column that a master/detail style would give.

Good luck

Iain