inner join/subqueries

[johnky]

I have a survey with 3 questions. I only want to select the 3 questions and answers from the people (id) who give 1 as answer (a) on question (q) 3.

|id|q|a|
|27|1|3|
|27|2|2|
|27|3|1|
|29|1|3|
|29|2|1|
|29|3|2|
|22|1|2|
|22|2|3|
|22|3|1|

With this query:
SELECT E2.id AS E2id, E2.q AS E2q, E2.a AS E2a FROM survey AS E1 INNER JOIN survey AS E2 ON E1.id = E2.id WHERE E1.a='1' AND E1.q='3'

i get these results:

|E2id |E2q|E2a|
|27 |1 |3 |
|27 |2 |2 |
|27 |3 |1 |
|22 |1 |2 |
|22 |2 |3 |
|22 |3 |1 |

Total 6 results, 3 results from 2 people who give 1 as answer on question 3.

So far no problem with the inner join query i'am using, but now i also want to delete this records and so far i now this is only possible by using subqueries.

 

[r937]

delete survey
  from survey
     , survey as E1
 where survey.id = E1.id
   and E1.a=1 
   and E1.q=3

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[johnky]

Thanks !