How to add a product in multiple categories?

Hi.

I would love to be very grateful if someone could help me. I have now 3 tables.

.tbl_cat
.tbl_sub
.tbl_product

tbl_cat
cat_id|cat_name
49|Comps
50|Toys

tbl_sub
sub_id|cat_id|sub_name
1|49|Notebook
2|49|Accessories
3|50|CaptainJack

tbl_product
id|cat_id|sub_id|prod_name|prod_specs
1|49|1|Asus Notebook|Contentofnotebook
2|49|1|Dell Notebook|Contentofnotebook
---

When i call the products from sub categorie, i use the following SQL String:

"select * from tbl_product a, tbl_sub b, tbl_cat c where a.CAT_ID = c.CAT_ID and a.SUB_ID = b.SUB_ID and a.CAT_ID = 49 and a.SUB_ID = 1 order by prod_name asc"

now i want a new record in products which would be both in the Notebook subdir, and accessories subdir. Can anyone help me please?

My mind is pretty confused.

 

I hope I understood your question, but it appears that you want to relate products to many subcategories. If so, the current design may not work for you. A product has a 1-to-1 relationship to both category and subcategory, what you need is a 1-to-many design. There are several options here, but one option is a relationship table such as:

productsubcategory (table)
--------------------------
productsubcategoryrecid
productrecid (id from tbl_product)
subcategoryrecid (sub_id from tbl_sub)
etc ...

I hope this helps, if not we can expand on it. Let me know.

 

Thank you, so far everything has been done. but i am stuck in another point.
Now my tables are like
----------------------------------------
tbl_cat
cat_id|cat_name
----------------------------------------
tbl_sub
sub_id|sub_name
----------------------------------------
tbl_product
id|prod_name
----------------------------------------
tbl_prodcats
id|prod_id|cat_id|sub_id
----------------------------------------

can you please me write an (asp) sql statement which will show me the records of cat_name="test"?

i use at the moment this:
select * from tbl_product a, tbl_sub b, tbl_cat c, tbl_prodcats d where d.CAT_ID = c.CAT_ID and d.prod_id=a.id and d.SUB_ID = b.SUB_ID and (CAT_NAME like '%test%' or SUB_NAME like '%test%')

if i have two categories like "Testing1" and "Testing2". Then this query shows two records from the product i want. I cannot group them, because if i group the cat_name should also be grouped and that will not solve my problem. Thanks in advance.

 

if you are a starter you better use a couple of SQLs and dont do joints

first SQL would be

second would be

this would be a better solution for you