Code: Select all
select DISTINCT products.* from products, color, products_color where ((color.color_id = '11' and products_color.color_id = color.color_id and products_color.product_id = products.product_id) and (color.color_id = '21' and products_color.color_id = color.color_id and products_color.product_id = products.product_id))products = product_id, product_name, product_type, etc. (multiple characterstics of individual product, keyed by product_id);
color = color_id, color_name;
product_color = product_id, color_id
This example is basically supposed to match results where the color_id is both 11 and 21 (in separate rows of the products_color table), but this query actually is checking the same row for both color_id's and getting nothing back, since each row only stores one color_id. I suspect that using some sort of subquery might be the solution, but I'm not sure.
Any help is greatly appreciated! Hope this all makes sense.