I need some help getting records based on a criteria where joining items table with their values table, having multiple search values.
Items table
+----+-----------+
| id | title |
+----+-----------+
| 82 | Test ITEM |
+----+-----------+
Attributes table
+----+--------------+
| id | Attribute |
+----+--------------+
| 1 | An Attribute |
+----+--------------+
Item Attribute Values table
+----+---------+--------------+-----------+
| id | item_id | attribute_id | option_id |
+----+---------+--------------+-----------+
| 1 | 82 | 1 | 25 |
| 1 | 82 | 1 | 3 |
+----+---------+--------------+-----------+
What i did:
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
But there is a problem with this query because it returns all items having 3 OR 25 values.
I want to return items only that has 25 and 3 values at the same time.
Tried this:
Advanced Mysql Query to get master record if two conditions matches on different rows of child records
Same result.
Please help.
Copyright Notice：Content Author:「Canser Yanbakan」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/47389039/mysql-get-records-having-multiple-values-on-a-different-relational-table-many