S.M. Nat S.M. Nat - 5 months ago 12
SQL Question

Mysql multi join query with list of ids

have 3 tables

products_to_stores

product_id | store_id
___________________
50 | 1
50 | 2
66 | 1
50 | 1
111 | 3
111 | 1
51 | 1
69 | 3
69 | 2


products_to_categories

product_id | category_id
____________________
50 | 69
50 | 68
50 | 40
51 | 66
52 | 55
69 | 41
111 | 40
111 | 70


product_descriptions

product_id | manufacturer_id (parent category id)
____________________
68 | 345
69 | 233
70 | 788
50 | 788
111 | 788
51 | 210
52 | 788


How to get list of product's ids within categories ids list 68,40,41,55,66... and with manufacturer's ids list 210,788,233.... and store_id=1 within one SQL query to avoid foreach loop with php and avoid duplicated products ids?

Answer
SELECT DISTINCT p.product_id FROM products_to_stores p INNER JOIN products_to_categories c ON c.product_id = p.product_id INNER JOIN product_descriptions d ON d.product_id = p.product_id 
WHERE 
manufacturer_id IN (210,788) AND category_id IN (40,44) AND store_id = 1
Comments