I need some complex query help...

[ppetree]

I'm not sure where to begin on this...

I have a survey engine that supports 7 types of questions and really an unlimited number of entries per question (yes/no being one exception).

So, I have 7 tables and each table correspondes to one answer type so I have:
response_bool (true/false or Yes/No)
response_date
response_multiple (check boxes)
response_rank (1-5)
response_single (radio button)
response_text

every table has question_id which corresponds to the question in the survey and a response_id corresponding to the respondent

What I want to do is to run a query which tells me how many questions each respondent has answered.

The following query returns all of the single responses (radio button) questions that were answered ordered by respondent

SELECT question_id, response_id FROM phpesp_response_single WHERE question_id > 118 AND question_id < 148 ORDER BY response_id;

What I dont know how to do it to get it to total the questions answered by each respondent for each table (question type).

I figure there has to be an easier way than doing the totals for each table by each respondent (there could be 1000's of respondents).

Any ideas?

Thanks!

[urtrivedi]

select response_id, count(*) tot_questions from 
(

select distinct response_id, question_id from response_bool 
union
select distinct response_id, question_id from response_date
union
select distinct response_id, question_id from response_multiple 
union
select distinct response_id, question_id from response_rank
union
select distinct response_id, question_id from response_single 
union
select distinct response_id, question_id from response_text

)
 WHERE question_id > 118 AND question_id < 148 
group by response_id
ORDER BY response_id;

[ppetree]

Wow! That was perfect! I new there was an easier way to do this!

I did get an error but it was easy to track down and fix... I had to an "AS t" to the end of the closing paren...

Thanks for that!