I was wondering if there are any SQL geniuses that can help me.
I am trying to write a query that will only return manufacturers that make products in a certain product category
the code that I wrote was the following (where XXXX is category_id)
SELECT image, a.manufacturer_id, name, b.store_id, category_id
FROM manufacturer a
INNER JOIN manufacturer_to_store b ON a.manufacturer_id = b.manufacturer_id
INNER JOIN product_to_store c ON c.store_id = b.store_id
INNER JOIN product_to_category d ON c.product_id = d.product_id
WHERE b.store_id = 1 AND category_id = XXXX ORDER BY sort_order,name
I am doing something wrong obviously as this either returns all manufacturers or it returns zero records... but never what I intend it to
any help or ideas would be appreciated
