SQL complex query

Question

Hi,

I have two tables: 'Category' and 'Product'. In Category I have catid and cat name. In product table I have item-id, item-name, catid.

What I need to do is show a result that will have item name and category name. But the category name will be multiple. So that one product can have multiple categories on it.

Answer 1

If your schema has a category ID in the product table, then no, one produce can't have multiple categories. If you have another M:N table to link products to categories, you should update your question.

Answer 2

You might want to create three tables, because of a join table to would allow each line to have multiple lines corresponding in the other table:

Category        : catid   catname
Product         : itemid  itemname
CategoryProduct : catid   itemid

So a product can have 0, 1 or more Categories.

Example content for the join table, for two products having the same two categories:

catid itemid        in CategoryProduct
  1     3
  1     4
  2     3
  2     4

Answer 3

In reply to your comment:

The category id of the product table is like 2,3,4 so 1 product can have multiple category

If you're allowed to change the table structure, by all means follow KLE's advice. That's the sane and maintainable approach.

If you can't change the table structure, you can query the categories with a hack like this:

select *
from product p
inner join category c 
    on ',' + p.catid + ',' 
        like '%,' + cast(c.catid as varchar) + ',%'