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. 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. 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
Archived
This topic is now archived and is closed to further replies.