[FIXED] select data on base of comparing multiple values between two columns in same table

Issue

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.

Solution

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 ) OR ( Question_Id = 3 AND Response_Id = 6 AND Response_Id = 7 ) OR ( Question_Id = 2 AND Response_Id = 4 ) GROUP BY user_id;

this will be your query if there are only 4, if there are multiple you could create an array with pair of Question_Id and Response_Id. loop through this array to create a where condition, store it in variable and pass in query.

Answered By - Parth Mahida