CODEWITHSUNDEEP
phpmysql
Pherrymason
Pherrymason

Reputation: 8078

MySQL: Get products having all attributes listed

I'm having some problems to get this query works as I expect.

I have three tables: products, product_attributes and attributes.
The relation is obvious (A product can have multiple attributes) 

products
---------
id

product_attributes
------------------
product_id
attribute_id

attributes
----------
id
name

What I want to achieve is to get those products that has a given list of attributes, BUT omit those products that only has a partial list of the desired attributes.
For example, having these products and attributes:

A query asking for those products with [blue,boy] would retrieve only Shoe 1.
A query asking for those products with [blue] would not return anything.

Since now I was working with this query:

SELECT p.*, pa.attribute_id
FROM products AS p 
LEFT JOIN product_attributes AS pa ON(pa.product_id=p.id)
WHERE 
pa.attribute_id IN(' . implode(',', $attr_ids) . ')
GROUP BY p.id
HAVING count(pa.attribute_id)=' . count($attr_ids)

This fails when just an attribute is given because it will return any product having that attribute.

Upvotes: 1

Views: 1407

Answers (2)

Saic Siquot
Saic Siquot

Reputation: 6513

-- PHP (or any other languaje) parts are hardcoded here!!!!

SELECT p.*, hma.howmuchattr
-- howmuchattr is needed by HAVING clause, 
-- you can omit elsewhere (by surrounding SELECT or by programming languaje)

FROM products AS p 
LEFT JOIN product_attributes AS pa ON pa.product_id = p.id 
LEFT JOIN (
    SELECT product_id, count(*) as howmuchattr
    FROM product_attributes 
    GROUP BY product_id
) as hma on p.id = hma.product_id

WHERE 
pa.attribute_id IN 
(1,3)                    -- this cames from PHP (or any other languaje). Can be (1) for the other case
GROUP BY p.id
HAVING count(*) = howmuchattr;

see sqlfiddle here
see also this answer

Upvotes: 2

Strawberry
Strawberry

Reputation: 33945

Aside from any other issues, this query...

  SELECT p.*
       , pa.attribute_id
    FROM products p 
    LEFT 
-- OUTER (this keyword is optional in MySQL)
    JOIN product_attributes pa 
      ON pa.product_id = p.id
   WHERE pa.attribute_id IN('$attr_ids')
   GROUP 
      BY p.id
  HAVING COUNT(*) = $cnt;

... is logically identical to...

  SELECT p.*
       , pa.attribute_id
    FROM products p 
-- INNER (this keyword is also optional in MySQL)
    JOIN product_attributes pa 
      ON pa.product_id = p.id
   WHERE pa.attribute_id IN('$attr_ids')
   GROUP 
      BY p.id
  HAVING COUNT(pa.attribute_id) = $cnt;

In order to maintain the utility of the OUTER JOIN consider rewriting as follows...

  SELECT p.*
       , pa.attribute_id
    FROM products p 
    LEFT 
    JOIN product_attributes pa 
      ON pa.product_id = p.id
     AND pa.attribute_id IN('$attr_ids')
   GROUP 
      BY p.id
  HAVING COUNT(pa.attribute_id) = $cnt;

Upvotes: 0

Related Questions