MS Access - SQL Query question

Question

System: Windows7, Access 2003, .mdb file, C# language, VS 2010

I'm doing queries with OleDbCommand, so I write the complete sql query in a string.

---

Table Survey  (Id)  
Table Questions  (Id,IdSurvey)  
Table Answers (Id,IdQuestion)  

the tables are simplified to show only relevant data

Assume 1 survey has 1..n questions
Assume 1 question has 0..n answers

I want to select only the surveys that have the answers 1 and/or 2 for the Question3, how do I do that?

Current (and poor) solution:

SELECT surv.Name FROM Survey surv WHERE 
(1 IN (SELECT answ.Number FROM Answers answ, Questions quest 
WHERE quest.Id = 3 AND quest.IdSurvey = surv.Id) 
AND/OR 
(2 IN (SELECT answ.Number FROM Answers answ, Questions quest
WHERE quest.Id = 3 AND quest.IdSurvey = surv.Id)))

This looks like crap, so I would appreciate help.

Thanks in advance.

---

EDIT

Better solution i came up with:

For 1 OR 2, the solutions given here are enough (for very simple logical expressions). Thanks guys

For 1 AND 2, i have created another solution, looks kinda good i think (again, for very simple logical expressions)

SELECT surv.Name FROM Survey surv WHERE EXISTS
(SELECT COUNT(*) FROM Answers answ, Questions quest 
WHERE quest.Id = 3 AND answ.Number IN (1,2) HAVING COUNT(*) > 1)

The "1" in the HAVING clause means that at least two lines are needed, and since the answers dont repeat for the same questions, this necesseraly means that at least the answers 1 AND 2 exists for the queried question.

Well, both the OR solution and the AND solution are bad when dealing with more complex logical expressions, like (1 AND (2 OR (4 AND (6 OR 7) ) ) ), truth is, its highly unlikely that such complexity will be needed, but i'd like to be safe. The ideal solution for me would be this:

SELECT surv.Name FROM Survey surv WHERE 
(1 IN (SELECT answ.Number FROM Answers answ, Questions quest 
WHERE quest.Id = 3 AND quest.IdSurvey = surv.Id) AS CANT_DO_THIS
AND/OR 
(2 IN CANT_DO_THIS) AND/OR 5 IN CANT_DO_THIS AND/OR 3 NOT IN CANT_DO_THIS)

Answer 1

SELECT surv.Name FROM Survery surv
    INNER JOIN Questions quest ON surv.ID = quest.IDSurvey
    INNER JOIN Answers answ ON quest.ID = answ.IDQuestion
    WHERE quest.ID = 3 AND answ.Number IN (1, 2)

This will return one line per survey (because you're linking in only question ID 3 and the single answer to that question), and only for surveys where the answer to question 3 is 1 or 2.

NOTE: It's not clear to me how you join the Questions and Answer table in your data model, so I assumed that the Answers table has a foreign key back to Questions called IDQuestion.

Answer 2

Select ...
From Survey As 
Where Exists    (
                Select 1
                From Questions As Q1
                    Join Answers As A1
                        On A1.IdQuestion = Q1.Id
                Where Q1.IdSurvey = S.Id
                    And Q1.Id = 3
                    And A1.Id In(1,2)
                Having Count(*) = 2
                )