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