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.
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;
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?
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)
