I have the following 4 tables (simplified for example) for a product filter scenario each product. Each product can have multiple attributes which have multiple values (but only ever one assigned to a product.
Code:
CREATE TABLE `products` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(150) NOT NULL,
)

CREATE TABLE `attributes` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(20) character set latin1 NOT NULL
)

CREATE TABLE `attributes_entity` (
  `product_id` int(11) NOT NULL,
  `attribute_id` int(11) NOT NULL,
  `value_id` int(11) NOT NULL,
   FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
   FOREIGN KEY (`attribute_id`) REFERENCES `attributes` (`id`),
   FOREIGN KEY (`attribute_id`) REFERENCES `attributes` (`id`)
)

CREATE TABLE `attributes_values` (
  `id` int(11) NOT NULL auto_increment,
  `value` varchar(255) default NULL,
)
I want to be able to retrieve a result of all the products that match all the filter values (attributes) passed through.
Code:
SELECT
    `p`.`id`
   `p`.`name`, 
FROM `products` `p
    INNER JOIN `attributes_entity` `ae` ON `p`.`id`=`ae`.`product_id`
    INNER JOIN `attributes` `a` ON `ae`.`attribute_id`=`a`.`id`
WHERE (`a`.`name`='size' AND `av`.`value`='12mm')
    --this is where it currently creates an "IMPOSSIBLE WHERE"
    AND (`a`.`name`='color' AND `av`.`value`='red')
It's easy to retrieve a product with a single WHERE cause because the inner joins create a single row relationship for each attributes. However as soon as you add an AND, and try to add a second attribute to filter by no single row meets both criteria but multiple (attribute) rows of the same `product` row will match all the criteria.

Is this possible with a single query or will I have to look at sub/multiple queries?