leetee Posted June 19, 2008 Share Posted June 19, 2008 Hi, I have a related products table below that basically links two product ID's together. From what I remember of normalisation this is called a composite key? My Problem is how to query this table and pull out prod_id and their relating prod_id from both columns. Related Products ----------------- [b]prod_id1 [/b], [b]prod_id2[/b] 446 , 18 446, 318 869, 18 $select = "SELECT p.id, p.product_code, p.name, p.category, p.subcat1, p.subcat2 FROM products as p INNER JOIN related_products as r ON p.id = prod_id1 INNER JOIN related_products as r2 ON p.id = prod_id2 WHERE r.prod_id1 = '$prod_id1' OR WHERE r2.prod_id2 = '$prod_id1 The above query doesn't work and I have tried all sorts of variations of this query. The best I can get is prod_id 446 has two related products (18 and 318). What I also need in addition to this is prod_id 18 has two related products (446 and 869). Hope this makes sense. Quote Link to comment https://forums.phpfreaks.com/topic/110963-help-with-query-composite-key-table/ Share on other sites More sharing options...
fenway Posted June 19, 2008 Share Posted June 19, 2008 That's backwards... you need to select from the related_products table and then join products in twice. Quote Link to comment https://forums.phpfreaks.com/topic/110963-help-with-query-composite-key-table/#findComment-569385 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.