SahebSoft SahebSoft - 25 days ago 17
SQL Question

Return result depend on conditions for multible rows without self join

I have a large answers table like this

person_id|question_id|answer
1|101|6
1|102|2
1|103|5
2|101|2
2|102|5
2|103|5
3|101|2
3|102|8
3|103|6
4|101|2
4|102|8
4|103|6
4|101|6
4|102|2
4|103|5


How to return persons depend on multiple question answers? For example I need to return person who answers:

6 for question 101

and 2 for question 102

and 5 for question 103


the query should return person 1 and 4

and consider that I need to filter depend on 10 questions, so I don't need to do 10 self join on the table :)

Answer

You can do this using group by and having:

select person_id
from t
where (question_id, answer) in ( (101, 6), (102, 2), (103, 5) )
group by person_id
having count(distinct question_id) = 3;

Note that the "3" needs to match the number of questions you have in the in list.

Comments