Return Single Row when Product is in multiple categories.

[w1nk5]

Ok. I have a products table, a categories table and a productcategories table.

How can I get a row from the products table when the product is in Category A and Category B.

He is some sample SQL code my search component is generating for a search for a product that is in 2 categories.

SELECT list.id,list.name,list.price,image.image 
FROM wp_wpsc_product_list AS list 
LEFT JOIN wp_wpsc_product_images AS image 
ON list.image = image.id 
LEFT JOIN wp_wpsc_item_category_assoc AS prodcat 
ON list.id = prodcat.product_id 
LEFT JOIN wp_wpsc_productmeta AS meta 
ON list.id = meta.product_id 
WHERE list.publish=1 
AND list.active=1 
AND prodcat.category_id=19 
AND prodcat.category_id=11 
AND list.price BETWEEN 751 AND 1000 
AND meta.meta_value = 'Chatham'

This is incorrect as it does not return any rows in the DB when there is a product in category 19 and 11 and all that meets all the criteria in the WHERE clause.

I obviously have either the joins or where clause structured incorrectly as the prodcat.category_id = 19 and prodcat.category_id = 11 are 2 seperate rows in the productcategory table. Anyways, I would really appreciate some help.

Cheers

[atomicnuke]

I'm still new at MySQL, but I ran into a similar problem, my syntax might be wrong, but you could try this. If not wait for someone that knows more, lol, just trying to possibly help.

SELECT list.id,list.name,list.price,image.image 
FROM wp_wpsc_product_list AS list 
LEFT JOIN wp_wpsc_product_images AS image 
ON list.image = image.id 
LEFT JOIN wp_wpsc_item_category_assoc AS prodcat 
ON list.id = prodcat.product_id 
LEFT JOIN wp_wpsc_productmeta AS meta 
ON list.id = meta.product_id 
WHERE list.publish=1 
AND list.active=1  
AND list.price BETWEEN 751 AND 1000 
AND meta.meta_value = 'Chatham'
AND prodcat.category_id IN (19, 11) 
GROUP BY list.id
HAVING COUNT(*) = 2;

[r937]

atomicnuke, your HAVING COUNT(*)=2 solution is excellent

but there may be a problem

the SELECT clause doesn't match the GROUP BY clause, thus invoking an obscure mysql extension to standard sql, which allows the query to run and return only one row per list.id, with an indeterminate image if there is more than one image

in fact, if a product has two images but is in only one category, then i think the query might return a false positive

wink, is there any way to restrict the selection to a single image? say, the one with the latest date or something?

by the way, those joins should all be INNER JOINs

[w1nk5]

I'm still new at MySQL, but I ran into a similar problem, my syntax might be wrong, but you could try this. If not wait for someone that knows more, lol, just trying to possibly help.

SELECT list.id,list.name,list.price,image.image 
FROM wp_wpsc_product_list AS list 
LEFT JOIN wp_wpsc_product_images AS image 
ON list.image = image.id 
LEFT JOIN wp_wpsc_item_category_assoc AS prodcat 
ON list.id = prodcat.product_id 
LEFT JOIN wp_wpsc_productmeta AS meta 
ON list.id = meta.product_id 
WHERE list.publish=1 
AND list.active=1  
AND list.price BETWEEN 751 AND 1000 
AND meta.meta_value = 'Chatham'
AND prodcat.category_id IN (19, 11) 
GROUP BY list.id
HAVING COUNT(*) = 2;

The 'IN' clause you are using replaces multiple OR statements.

For example, in stead of prodcat.category_id = 19 OR prodcat.category_id = 11 you could simply use prodcat.category_id IN (19,11).

I, however, need to replace a combination of multiple AND and OR statements for the prodcat.category_id column. For example, lets say where in prodcat.category_id = 2 AND (prodcat.categoy = 1 OR prodcat.category =3)

[w1nk5]

atomicnuke, your HAVING COUNT(*)=2 solution is excellent

but there may be a problem

the SELECT clause doesn't match the GROUP BY clause, thus invoking an obscure mysql extension to standard sql, which allows the query to run and return only one row per list.id, with an indeterminate image if there is more than one image

in fact, if a product has two images but is in only one category, then i think the query might return a false positive

wink, is there any way to restrict the selection to a single image? say, the one with the latest date or something?

by the way, those joins should all be INNER JOINs

Why INNER join instead of LEFT join?

[r937]

because there will be no unmatched rows

why did you think you needed LEFT join instead of INNER?

[r937]

For example, lets say where in prodcat.category_id = 2 AND (prodcat.categoy = 1 OR prodcat.category =3)

HAVING COUNT(CASE WHEN category = 2 THEN 'ok' END) = 1
   AND COUNT(CASE WHEN category IN (1,3) THEN 'ok' END) > 0

[w1nk5]

HAVING COUNT(CASE WHEN category = 2 THEN 'ok' END) = 1
   AND COUNT(CASE WHEN category IN (1,3) THEN 'ok' END) > 0

I don't get it. What's with the THEN 'ok'?

[w1nk5]

Hey Rudy, just realized who you are.

I have your book Simply SQL, I'm gonna use it now to try figure this out.

Thanks for your help.

Cheers

[r937]

I don't get it. What's with the THEN 'ok'?

it is countable

it could actually have been any non-null value