I have a table containing attributes with the following structure:
id: bigint unsigned autoincrement
product_id: bigint foreign key
attribute_id: bigint foreign key
value: varchar(100)
I can query one criteria in the following fashion:
SELECT DISTINCT product_id FROM product_attributes WHERE attribute_id = ? AND value = ?
However I need to be able to find products that match multiple such criteria and would like to avoid multiple database queries for performance reasons. Simply adding more criteria with AND won't work since they will involve the same columns so for example:
SELECT DISTINCT product_id FROM product_attributes WHERE attribute_id = 1 AND value = 'Blue'
INTERSECT
SELECT DISTINCT product_id FROM product_attributes WHERE attribute_id = 2 AND value = '36'
INTERSECT
SELECT DISTINCT product_id FROM product_attributes WHERE attribute_id = 3 AND value = 'slim'
I have read about the INTERSECT statement which seems like it might work but I've read that MySQL doesn't support it, a search through MySQL 8 documentation produced no relevant result and the query above which I assume is correct produces an error on MySQL.
I've also read that something similar could be achieved with an inner join, but all the examples I've found involve multiple tables. There might also be an even better or simpler way to write the query that hasn't occurred to me. Or perhaps it's actually better to just send multiple queries and calculate the intersection outside of MySQL (though I would be very surprised) I appreciate greatly any help from anyone who has done something similar in the past.
You need to use aggregation to count the number of matching rows to the set of conditions and assert that it is equal to the number of conditions:
SELECT product_id
FROM product_attributes
WHERE (attribute_id, value) IN ((1, 'Blue'), (2, '36'), (3, 'slim'))
GROUP BY product_id
HAVING COUNT(*) = 3