[RESOLVED] MySQL product multi filter

[knowj]

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.

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.

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?

[knowj]

Updated code to add in the missing av.values join:

SELECT
    `p`.`id`,
    `p`.`name`
FROM `attributes_entity` `ae`
    INNER JOIN `products` `p` ON `ae`.`product_id`=`p`.`id`
    INNER JOIN `attributes` `a` ON `ae`.`attribute_id`=`a`.`id`
    INNER JOIN `attributes_values` `av` ON `ae`.`value_id`=`av`.`id`
WHERE (`a`.`perma_name`='size' AND `av`.`value`='12mm')
AND (`a`.`perma_name`='color' AND `av`.`value`='red')

If I use an OR for the 2nd condition I get half way there to the desired result:

SELECT
    `p`.`name`
FROM `attributes_entity` `ae`
    INNER JOIN `products` `p` ON `ae`.`product_id`=`p`.`id`
    INNER JOIN `attributes` `a` ON `ae`.`attribute_id`=`a`.`id`
    INNER JOIN `attributes_values` `av` ON `ae`.`value_id`=`av`.`id`
WHERE `p`.`delete`!='1'
AND (`a`.`perma_name`='size' AND `av`.`value`='12mm')
OR (`a`.`perma_name`='color' AND `av`.`value`='red')

The 2nd query returns the following
product 1 /* matching size=12mm */
product 1 /* matching color=red */
product 2 /* matching size=12mm */

However I only want it to return results matching both/all of the conditions but at the same not creating an "IMPOSSIBLE WHERE"

[knowj]

Resolved:

SELECT
    `p`.`name`
FROM `attributes_entity` `ae`
    INNER JOIN `products` `p` ON `ae`.`product_id`=`p`.`id`
    INNER JOIN `attributes` `a` ON `ae`.`attribute_id`=`a`.`id`
    INNER JOIN `attributes_values` `av` ON `ae`.`value_id`=`av`.`id`
WHERE `p`.`delete`!='1'
AND (`a`.`perma_name`='size' AND `av`.`value`='12mm')
OR (`a`.`perma_name`='color' AND `av`.`value`='red')
GROUP BY `p`.`id` HAVING COUNT(`a`.`id`)=2