SQL: Joining table multiple times

[ultim]

I need some sql help!

I have a product table and a category table. Each product can be in 1 or 2 categories.

CATEGORY TABLE
PKCatID Name
1, Cat 1
2, Cat 2
3, Cat 3

PRODUCT TABLE
ProductID FKCatID1 FKCatID2 Product Name
1, 2, NULL, Product 1
2, 2, 1, Product 2
3, 1, 3, Product 3

I need an sql query to return me all the products in different categories. i.e. return me 5 rows.

Product 1, Cat 1
Product 2, Cat 2
Product 2, Cat 1
Product 3, Cat 1
Product 3, Cat 3

How do i achieve this in sql?

 

[Haircut]

Normalise your tables properly and it will be a piece of cake.

You need to have a third table product_category or similar that specified what categories a product is in.
i.e. for Product 1 you would have a single entry as follows:

ProductID CategoryID
1         1

and Product 2 would have two entries, as follows:

ProductID CategoryID
2         2
2         1

As well as being better db design, you can then just join the three tables to get the output you want.

 

[brainchylde]

I'd have to agree with the poster above. The datatable is poorly designed. However, you will be able to get around it using the something like the following :

SELECT     PRODUCT_CATEGORIES.PRODUCT_NAME, dbo.CATEGORY.CATEGORY_NAME
FROM         (SELECT     PRODUCT_NAME, CATEGORY1 AS CATEGORY
                       FROM          dbo.PRODUCT
                       WHERE      (CATEGORY1 IS NOT NULL)
                       UNION
                       SELECT     PRODUCT_NAME, CATEGORY2 AS CATEGORY
                       FROM         dbo.PRODUCT AS PRODUCT_1
                       WHERE     (CATEGORY2 IS NOT NULL)) AS PRODUCT_CATEGORIES INNER JOIN
                      dbo.CATEGORY ON PRODUCT_CATEGORIES.CATEGORY = dbo.CATEGORY.CATEGORY_KEY

 

[ultim]

Thanks for the replies.

I understand it is a poor db design. I had to bolt this 2nd category in.

 

[Dolph]

Thanks for the replies.

I understand it is a poor db design. I had to bolt this 2nd category in.

You still need to normalise the design really, otherwise it's never going to work very well.

Create the link table and use the existing data to populate it from the products table using an append query.

You're going to have to redo the queries whatever you do, so it's much better to do it properly now.