Mysql获取不同(关系)表上具有多个值的记录(manyToMany)

Question

我需要一些帮助，根据将项目表与其值表连接起来并具有多个搜索值的条件来获取记录。

项目表

+----+-----------+
| id |   title   |
+----+-----------+
| 82 | Test ITEM |
+----+-----------+

属性表

+----+--------------+
| id |  Attribute   |
+----+--------------+
|  1 | An Attribute |
+----+--------------+

项目属性值表

+----+---------+--------------+-----------+
| id | item_id | attribute_id | option_id |
+----+---------+--------------+-----------+
|  1 |      82 |            1 |        25 |
|  1 |      82 |            1 |         3 |
+----+---------+--------------+-----------+

我做了什么:

SELECT 
p.*
FROM 
product_products p 
LEFT JOIN
product_attribute_values v ON v.product_id = p.id
LEFT JOIN
product_attribute_options o ON o.id = v.option_id
WHERE 
(p.id = 82 OR p.parent_id = 82) AND
(o.id IN (25, 3))
GROUP BY p.id

但是此查询存在问题，因为它返回具有 3 OR 25 个值的所有项目。

我只想返回同时具有 25 和 3 个值的项目。

尝试过这个: Advanced Mysql Query to get master record if two conditions matches on different rows of child records

同样的结果。

请帮忙。

Answer 1

尝试这个查询

SELECT p.id,p.title
FROM product_products p 
LEFT JOIN product_attribute_values v ON v.product_id = p.id
LEFT JOIN product_attribute_options o ON o.id = v.option_id
WHERE (p.id = 82 OR p.parent_id = 82)
  AND (o.id IN (25, 3))
GROUP BY p.id,p.title
HAVING COUNT(DISTINCT o.id) = 2

添加option_id

SELECT p.*,o.id
FROM
  (
    SELECT p.id,p.title
    FROM product_products p 
    JOIN product_attribute_values v ON v.product_id = p.id
    JOIN product_attribute_options o ON o.id = v.option_id AND o.id IN (25, 3)
    WHERE (p.id = 82 OR p.parent_id = 82)
    GROUP BY p.id,p.title
    HAVING COUNT(DISTINCT o.id) = 2
  ) p
JOIN product_attribute_values v ON v.product_id = p.id
JOIN product_attribute_options o ON o.id = v.option_id AND o.id IN (25, 3)