Multiple Products Having multiple categories

I have following tables: Products & Categories
Products contains: ID, Name, CatID and etc...
Categories contains: ID, Name

Lets say product 'x' have multiple CatID & are stored 1,2,3.4.......
If i want to display products of CatID 3 it doesn't display. But product which has CatID as 3 only displays.

Why is it not capturing '3' CatID for product 'x' as mentioned above.

So then product 'z' has only 1 CatID as : '3' This doesnt display the product.

Why not x?

How are you displaying it? Usually you would use a relational table if one product could be in multiple categories... so you would have:


Products contains: ID, Name and etc... (notice no CatID)
Categories contains: ID, Name
ProductCategories contains: ProdID, CatID

So that if the ProductCategories table had two rows:
1, 1
1, 2

Then productID 1 would be in both category 1 and category 2... that way you can use inner joins to relate the three tables.

It is possible to do it with two tables as you mention, but you would have to depend on LIKE searches and couldn't use standard SQL joins.

I had used this same logic as you said earlier. adding multiple records for categories. But i faced problem in edit records
How will edit product page work with same above example given by you.
Like Product 'x' has two category another category needs to be added or change or removed.. How will edit-product page work?
What are thr RS required & how to link?

In add product page i used repeat region on Categories & then checkbox for them.

i got no problem in add product page but facing problem in edit-product!

attached file contain insert record how will edit work for this?

You can use the "Manage Relational Table" server behavior on the update page and use a multi-select list or checkbox group. This is something we could help with in a premium support ticket if you get stuck.