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.
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.
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
AND list.price BETWEEN 751 AND 1000
AND meta.meta_value = 'Chatham'
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.