Mysql Select Query Clarification

Question

I have a doubt on following query, please help me on this one,

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

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

My table has values:

Answer 1

I suspect that what you want here is all students who match both of the conditions. One approach is to aggregate by student, and then assert the conditions:

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;

Demo