mysql获取属性-列头,MySQL：获取列出了所有属性的产品

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:

Shoe 1 [blue,boy]

Shoe 2 [blue,girl]

Shoe 3 [red,boy]

Shoe 4 [red,girl]

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.

解决方案-- 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;