Skip to content
Advertisement

How can I apply mutliple filters on my sql tables

I am struggling with what looks to be relational division in sql. I need to filter data from the same table based on multiple criteria. Here-under is a schema of what my table looks like.

| -------|----------------|-----------------|----------------|
|ID      |Question        |RespondentId     |Answer          |
| -------|----------------|-----------------|----------------|
|1       |Big             |1                |Yes             |
|2       |Big             |2                |Yes             |
|3       |Big             |3                |No              |
|4       |Gender          |1                |Male            |
|5       |Gender          |2                |Female          |
|6       |Gender          |3                |Female          |
|7       |Children        |1                |No              |
|8       |Children        |2                |Yes             |
|9       |Children        |3                |No              |
--------------------------------------------------------------

I need the RespondenIds from this table called Answers that match the following filters : Question = Big and Children and Answer = Yes and Yes respectively for every question. Therefore, if I would have a correct Sql query my result should return me the following array : [2] since the only row that has the answer Yes for the question Big and the answer Yes for the question Children is the one with RespondentId = 2.

Also, the questions and the answers provided are not fixed and should be modular. For instance, I should be able to change an answer or removing a question without having to change the whole structure of my query.

Could you please help me finding a correct query for this problem ? I have been looking to a lot of explanations provided by @Erwin Brandstetter but none of them match my needs.

Advertisement

Answer

I would do this as:

select a.RespondentId 
from Answers a
when (question, answer) in ( ('Big', 'Yes'), ('Children', 'Yes') )
group by RespondentId
having count(*) = 2 ;

This is easily generalized to:

with qa as (
      select v.*
      from (values ('Big', 'Yes'), ('Children', 'Yes')
     ) v(question, answer)
select a.RespondentId 
from Answers a join
     qa
     on a.question = qa.question and a.answer = qa.answer
group by RespondentId
having count(*) = (select count(*) from qa);

This is pretty generalizable. You could even arrange the CTE to take an array or json argument and parse out into the separate comparison values.

Advertisement