SQL Select based on multiple criteria

[theKernel]

Perhaps some can provide a lead / suggestion on how I can approach this problem.

I have a database which records people's answers to questions (basically a survey). There are 3 main tables, person, personAnswer and question. personAnswer is a many to many containing the question id, person id, and the answer text (foreign keys to the person table and the question table).

Basically I need to develop a statement that will allow to select a single person based on her answer to multiple questions. For example he answered yes to question id 5 and no to question id 6. The statement I am trying to develop would need to be scalable (i.e. for 3,4,5 or more separate questions). Is this possible or do the queries need to be run separately and then joined in php or otherwise?

Any suggestions? Joins? Unions? Something else?

Cheers,
Tyler

 

[rac2]

select a single person based on her answer to multiple questions. For example he answered yes to question id 5 and no to question id 6.

What if two people answered the same way? Would you wish to see everyone who answered yes to question 5 and no to question 6? If not how will you pick one?

It is possible to write a single query that uses different sets of criteria to retrieve rows. Provided there is a fixed number of possible criteria. The query works for particular questionnaire. If the questionnaire changes then you would need to revise the query.

The technique is to use two conditions for each criterion, one condition is whether the criterion is relevant, the second is the value of to check. For two criteria the query would be

SELECT * FROM MyTable
WHERE ( @anyValueColA = true OR colA = @searchValueColA)
  AND (@anyValueColB = true OR colB = @searchValueColB)

A variation of this technique is to treat some value of the search variable as meaning the criterion is not relevant. Or to leave the search varialbe empty or null.

SELECT * FROM MyTable
WHERE ( @searchValueColA IS NULL OR colA = @searchValueColA)
  AND (@searchValueColB = 'I dont care' OR colB = @searchValueColB)