Query

In this page must be list of all surveys - and (of course ) add/edit/remove buttoms above. With those buttons Administrator be able to add remove and edit each survey details. Besides, there  must be "View Results" link -that will lead to "all_answerers" page and publish/unpublish button -to determine if this survey will be visible to users.

And the whole page must look something like this:

First of all:
to populate the surveys list it is simple select query ,but how to display here also the number of questions and number of users answered? little more complicate...
I devided the query tasks to few substeps and the things started to look little easier:

1. to build view of all surveys with users who answer them:

SELECT DISTINCT U.user_id, S.survey_id

FROM surveys AS S LEFT OUTER JOIN

questions AS Q ON Q.surv_id = S.survey_id LEFT OUTER JOIN

users_answers AS U ON U.question_id = Q.questionID

2. to build view of each survey and it's answerers count:

SELECT  survey_id, COUNT(user_id) AS answerersNUM

FROM   

(SELECT DISTINCT U.user_id, S.survey_id

FROM   surveys AS S LEFT OUTER JOIN

   questions AS Q ON Q.surv_id = S.survey_id LEFT OUTER JOIN

   users_answers AS U ON U.question_id = Q.questionID)

GROUP BY survey_id

3. to buid view of all survey data and count of its questions:

SELECT     surveys.survey_id, surveys.name, surveys.description,

 surveys.IsPublished,

COUNT(Q.questionID) AS QuestionNum

FROM   surveys LEFT OUTER JOIN

questions AS Q ON Q.surv_id = surveys.survey_id

GROUP BY surveys.survey_id, surveys.name,

 surveys.description, surveys.IsPublished

4. to merge the last and the second views:

SELECT     surveys.survey_id, surveys.name, surveys.description,

surveys.IsPublished, COUNT(Q.questionID) AS QuestionNum, SA.answerersNUM

FROM         surveys INNER JOIN

(SELECT     survey_id, COUNT(user_id) AS answerersNUM

FROM  (SELECT DISTINCT U.user_id, S.survey_id

FROM  surveys AS S LEFT OUTER JOIN

questions AS Q ON Q.surv_id = S.survey_id LEFT OUTER JOIN

users_answers AS U ON U.question_id = Q.questionID) AS SHLAV1

GROUP BY survey_id) AS SA ON SA.survey_id =

surveys.survey_id LEFT OUTER JOIN

questions AS Q ON Q.surv_id = SA.survey_id

GROUP BY surveys.survey_id, surveys.name,

surveys.description, surveys.IsPublished, SA.answerersNUM

Well thats it; the query little complicated but its working fine...






Download the SqlScript of creating DB tables link