Post by rossradiation » Mon May 02, 2011 9:43 pm

Hi,
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
:drunk:

Newbie

Posts

Joined
Mon May 24, 2010 8:33 pm

Post by ckonig » Mon May 02, 2011 9:53 pm

this should work:

Code: Select all

SELECT DISTINCT manufacturer.manufacturer_id, manufacturer.name
FROM  `product` 
JOIN manufacturer ON ( product.manufacturer_id = manufacturer.manufacturer_id ) 
JOIN product_to_category ON ( product.product_id = product_to_category.product_id ) 
JOIN category ON ( product_to_category.category_id = category.category_id ) 
JOIN product_to_store ON ( product.product_id = product_to_store.product_id ) 
WHERE category.category_id =XXX
AND store_id =YYY

User avatar
Active Member

Posts

Joined
Wed Feb 16, 2011 4:26 pm
Location - Netherlands

Post by rossradiation » Mon May 02, 2011 10:13 pm

wow! thank you very much for replying so quickly!
it worked perfectly too!
cheers!

Newbie

Posts

Joined
Mon May 24, 2010 8:33 pm
Who is online

Users browsing this forum: No registered users and 34 guests