Combining data from two queries in SQL Server 2005

Question

I have the following query which returns a list of questions and the possible answers for each question:

SELECT
sq.question_id, sq.question_text, qo.question_option_id, qo.option_text

FROM
 dbo.survey_question sq
LEFT OUTER JOIN dbo.question_option qo on sq.question_id = qo.question_id

ORDER BY
sq.question_id

I also have the following query which returns how many times a particular answer has been chosen:

SELECT ra.question_id, ra.question_option_id, count(*) AS Total
FROM
dbo.form_response_answers ra
GROUP BY ra.question_option_id, ra.question_id

I need to combine these two queries so that the results returned list all questions/possible answers (like the first query) in addition to how many times that answer was chosen.

I tried making a VIEW out of the second query and doing a couple of OUTER JOINS from the first query to the second but I could not make it work. Would someone point me in the right direction on this?

Alain · Accepted Answer · 2012-04-09 13:58:24Z

1

Try this:

SELECT  sq.question_id, sq.question_text, qo.question_option_id, qo.option_text, G.Total
FROM    dbo.survey_question sq
LEFT OUTER JOIN dbo.question_option qo 
    ON sq.question_id = qo.question_id
LEFT JOIN
(SELECT ra.question_id, ra.question_option_id, count(*) AS Total
 FROM dbo.form_response_answers ra
 GROUP BY ra.question_option_id, ra.question_id ) G
    ON G.question_id = sq.question_id AND G.question_option_id = qo.question_option_id
ORDER BY
sq.question_id

answered Apr 9, 2012 at 13:58

Alain

27.4k21 gold badges124 silver badges198 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Combining data from two queries in SQL Server 2005

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related