Mysql – select data on base of comparing multiple values between two columns in same table

join;MySQLPHP

enter image description here

question id (1,3,2,3) responses id(1,6,4,7)

     Question_Id   Response_Id
               1 =>1
               3=>6
               2=>4
               3=>7

I need to select those distinct users from users_response(following) table who answered all questions mentioned above and have the same answer as mentioned under response_id (1,6,4,7) to the question_id(1,3,2,3) respectively.

Result should be only '2'.

Can you please help me or give me some hint to deal with it. Any dynamic way because count of question_id and response_id can be increased. like in this example here are only total 4 conditions are given but it could go up or down.

 SELECT * FROM users_responses
 WHERE 
   (Question_Id = 1 AND Question_Id = 3) 
 AND Question_Id = 2 AND Question_Id = 3 
 AND (Question_Id = 1 AND Response_Id = 1);

but I'm getting no row while there is at least one row with user_id 2 is fulfilling conditions.

Best Answer

There are many solutions for this kind of problem (it's called relational division).

The most simple ways to write it would be either by self-joining the table (4 times) or with a GROUP BY and HAVING clause:

SELECT user_id
FROM users_responses
WHERE (question_id = 1 AND response_id = 1)
   OR (question_id = 3 AND response_id = 6)
   OR (question_id = 2 AND response_id = 4)
   OR (question_id = 3 AND response_id = 7)
GROUP BY user_id
HAVING COUNT(DISTINCT question_id, response_id) = 4 ;

If there is a UNIQUE constraint on (user_id, question_id, response_id), you can simplify the HAVING clause to:

HAVING COUNT(*) = 4 ;
Related Question