# select data on base of comparing multiple values between two columns in same table 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.

## 1 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 ;
``````
• This query giving 2 resords user id 2,6 while user 6 has not provided 1,6,4,7 answer to question 1,3,2,3. Result should be only 2 but user id 6 is also coming in output so how to deal with it. – coder Nov 12 '16 at 15:53
• @coder did you try this exact query? – ypercubeᵀᴹ Nov 12 '16 at 17:12
• working perfectly , my mistake. can you please tell little bit about the functionality of "having count(DISTINCT question_id, response_id) =4" in this query. @ypercube – coder Nov 13 '16 at 3:01