CODEWITHSUNDEEP
sqlpostgresqlintersectrelational-division
Mr.Fabulous
Mr.Fabulous

Reputation: 116

Is there a more efficient way of selecting the data than multiple intersects SQL

I have data in my PostgreSQL database in the format below

answer_id    question_id    country_id    answer
    1             1              1           7
    2             1              2           7
    3             1              3           5
    4             2              1           3
    5             2              2           2    
    6             2              3           2

What I am trying to do is get all countries which have a certain answer for a certain country, and we can have multiple question~answer combination. For example I can need all countries which for question 1 have 7 for an answer (2 values), but then, along with the first condition) I also add that answer for question 2 is 2 and now it drops from 2 values (countries under ids 1 and 2) to only 1 (country id 2).

Now I have managed to do it with intersect as it follows...

select country_id from answer_table where question_id = 1 and answer = 7
intersect 
select country_id from answer_table where question_id = 2 and answer = 2

Problem is that I need to be able to do this dynamically, meaning that one time I may select only 1 question~answer pair, but other times I may want more (3, 5, 7 or whatever) which affects the number of selects (and in turn intersects).

I mean this above works and I do have a capability to use a query builder so it really isn't a big deal to generate, but I don't believe that it is the most efficient nor the smartest way.

Therefore, my question is basically is there a more efficient or smarter way of doing these selects/intersects dynamically (like function which takes arrays of data or whatever?)?

Thank You and have a good one!

p.s. I found this stack thread, but there they use fixed 5 queries at all times.

Upvotes: 0

Views: 147

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

I don't know if it is more efficient, but you might find it more generalizable:

select country_id from answer_table
where (question_id, answer) in ( (1, 7), (2, 2) )
group by country_id
having count(distinct (question_id, answer) ) = 2;

You can actually replace the in list and "2" with array functions to pass in array values.

Upvotes: 1

Related Questions