How to retrieve data of a product which falls under two categories?

I'm using MS SQL 2000. I developing a shopping cart where on the admin
side when we are inserting or updating the products, there is a chance
that a single product can fall into two categories therefore i'm giving
the users a option of CheckBoxList control for the categories. The
checkboxlist control is Database Databinded with categories.
When a user selects couple of checkboxes, i'm storing both the
categories seperated with a comma in a single field of database but
then when i want retrieve the all products of a particular category,
this approach wont work. Is their any way around for this'
Even though i use CheckboxList control is their any way to retrieve
products of a particular category' Can i store each checkbox text in a
different row with the same product ID's in a different table and all
the product info in a different table ' I hope i made myself clear.
Thanks for your help and time in advance.it's not particularly clear I'm afraid.
It's a good idea to make sure that all data stored in your database is
atomic (just one value). by storing data with comma separators you are
breaking with this, and it is the start of a very bad road to go down,
as you have just discovered.
One possible answer to your question if you are to insist on keeping
with this data structure is to match on WHERE Category LIKE '%' +
@.Category + '%' . However if you have a category that is a subset of
another (e.g. Name and product name) then you'll have problems with
this.
A much better idea is to store your data in a relational fashion, so
from what I can tell (which is a bit of a guess), you'd want 3 tables
here:
tblCategories (CategoryID int, Category varchar(50))
tblProducts(ProductID int, Product varchar(50))
tblProductCategories(ProductID int, CategoryID int)
the first 2 tables store your information about the 2 entities, the
third stores the relationship between the 2.
You could then in your UI call something to insert rows into
tblProductCategories for each checkbox, then you could more easily
query your data.
Hope this helps, otherwise you might need to post a bit more
information about what you need, I especially don't understand how the
checkboxes fit in to all this.
Cheers
Will|||Thank you very much for your time and help
I think i got the way out with the advice you gave me
thank you very much for that
I have worked out the following way and its working
i know there might be more efficient ways of doin this
As said above i got three tables
tblCategories (CategoryID int, Category varchar(50))
tblProducts(ProductID int, Product varchar(50))
tblProductCategories(ProductID int, CategoryID int)
on the UI side whenever I'm inserting the Product info
i'm grabbin the last inserted Product ID using @.@.IDENTITY
now, i'm loopin the each item in checkboxlist
and if Selected then inserting the CheckboxID (CategoryID)and the
previously grabbed ProductID, therefore the product key will
be same for all the categories.
And now by the joining the Products table with the ProductsCategories
i'm able to retrieve all the necessary info about Products
Thanks mate