Mysql Query needs clarification

Question

Please i need solution for this, i wrote a query

SELECT * FROM test 
WHERE (field_id  = 87 and value in ("xxxx")) AND (field_id = 88 and value in ("R,A"))

this results empty rows, how to write this query i want to match id and value using "AND" condition,

My Table:

Solution gave was:

SELECT student_id
FROM yourTable
GROUP BY student_id
HAVING
    SUM((field_id, value) = (87, 'xxxx')) > 0 AND
    SUM((field_id, value) = (88, 'R')) > 0;

though it uses assertion and it doesn't fetch for "in" condition like value in ("R","A")

please help, it should match field_id = 87 and value in ('R','A') like this

Answer 1

Try this:

SELECT * FROM test WHERE (field_id = 87 and value in ("xxxx")) OR (field_id = 88 and value in ("R", "A"))

Please note OR instead of AND, and quotes in value in ("R", "A").

OR because you're checking each row against a condition and one row cannot have both field_ids.

Quotes because you quote each value, not the values list.

Answer 2

I would write your query as:

SELECT student_id
FROM test
GROUP BY student_id
HAVING
    SUM((field_id, value) = (87, 'xxxx')) > 0 AND
    SUM((field_id, value) = (88, 'R')) > 0 AND
    SUM((field_id, value) = (88, 'A')) > 0;