Search code examples
mysqlentity-attribute-value

MYSQL query help (EAV Table)

I have the following query to retrieve customers who answer YES to a particular question "OR" NO to another question.

SELECT customers.id
FROM customers, responses
WHERE (
(
responses.question_id = 5
AND responses.value_enum = 'YES'
)
OR (
responses.question_id = 9
AND responses.value_enum = 'NO'
)
)
GROUP BY customers.id

Which works fine. However I wish to change the query to retrieve customers who answer YES to a particular question "AND" answer NO to another question.

Any ideas on how I can achieve this?

PS - The responses above table is in an EAV format ie. a row represents an attribute rather than a column.

Solution

  • I'm assuming that you have a column called customer_id in your responses table. Try joining the responses table to itself:

    SELECT Q5.customer_id
FROM responses Q5
JOIN responses Q9 ON Q5.customer_id = Q9.customer_id AND Q9.question_id = 9
WHERE Q5.question_id = 5
AND Q5.value_enum = 'YES'
AND Q9.value_enum = 'NO'