Showing A Part In More Than One Place

[winston1984]

I only have one instance of a Product in the database. Each part has a partNumber, etc, and a ProdGroup ( a 4 digit numerical number corresponding to a Grouping, e.g. Handlebars ), then two more fields are DuplicateGroup1 and DuplicateGroup2. These are 4 digit numerics as well. For example, a set of handlebars can be Group 400 (BMX) but DuplicateGroup1 300(Mountain Bike). I have a report which generates a price list and orders the products in Group Order, so grouped up so the reader can see parts for different types of bikes and groups. But what I want, in the report is to have one product (handlebars) showing in the BMX section and also in the Mountain Bike section. But there is only one instance of the PartNumber. My query is below, but I still only get one instance and it it not shown in the report for Duplicate Groups.

SELECT dbo_Product.IDPartNumber
FROM (dbo_ProdGroup INNER JOIN (dbo_Product INNER JOIN dbo_Stock ON dbo_Product.IDPartNumber = dbo_Stock.IDPartNumber) ON dbo_ProdGroup.ID = dbo_Product.GroupCode) INNER JOIN dbo_Brand ON dbo_Product.ProductBrand = dbo_Brand.Brand
WHERE (dbo_Product.IDPartNumber=[dbo_Stock].[IDPartNumber]) AND (dbo_Product.ProductBrand=[dbo_Brand].[Brand]) AND ((dbo_ProdGroup.ID=[dbo_Product].[DuplicateGroup2]) OR (dbo_Product.GroupCode=[dbo_ProdGroup].[ID]) OR (dbo_ProdGroup.ID=[dbo_Product].[DuplicateGroup1]))

Thanks for the help.

Regards,

Lewis

 

[RickSpr]

This would be very difficult, if it is possible at all, and I'm not sure it is.

The problem is in your table structure. Clearly a product is associated with multiple groups, and a group with multiple products. This is a many-to-many relationship. If I understand what you've done, you've tried to simulate that by having several one-to-many relationships from Product to Group, using several foreign keys. That will ALWAYS cause trouble.

The preferred way to represent a many-to-many relationship is with an intermediate table. In this case, that table would have fields for the Group key and the Product key. An example using the data you gave would be like this:

TABLE ProductGroups
Product Group
Handlebars 400
Handlebars 300
Saddle 300

The beauty of this approach is that you can join the ProductGroups table with either the Groups table or the Products table. For example:

Groups INNER JOIN ProductGroups:
Group GroupName Product
300 MountainBike Handlebars
300 MountainBike Saddle
400 BMX Handlebars

Products INNER JOIN ProductGroups:
Product Group
Handlebars 300
Handlebars 400
Saddle 300

As you can see, these joins automatically make your product appear multiple times. For your report, you might need to join all three tables to get the result set you need.

Using an intermediate table, neither the Groups table nor the Products table would have any foreign keys for the other. Unfortunately, if you have much of the application already developed, that means you probably have a lot of changes to make in order to implement this approach. Nevertheless, it's worth it--take my word for it. If you keep the table structure you have now, the kind of problem you have now will happen more and more frequently as the application grows, and you'll probably find that it causes you to have bugs that will be very difficult to kill without creating more, even subtler, bugs.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein