How to perform a OUTER JOIN on two complex SQL queries?

Question

I have two relatively complex queries that I am trying to join together into one result set.

Result set 1:

SELECT  sq.question_id,     
    COUNT(ra.question_option_id) AS TotalAnswers

FROM    dbo.survey_question sq
    LEFT OUTER JOIN dbo.question_option qo
        ON sq.question_id = qo.question_id
    LEFT OUTER JOIN dbo.form_response_answers ra
        ON qo.question_option_id = ra.question_option_id
GROUP BY sq.question_id

Result set 2:

SELECT  p.program_id, 
    p.pre_survey_form_id, 
    p.post_survey_form_id, 
    fq.form_id, 
    sq.question_id, 
    sq.question_text, 
    qo.question_option_id, 
    qo.option_text, 
    G.Total

FROM    dbo.program p
    LEFT OUTER JOIN dbo.form_question fq
        ON p.pre_survey_form_id = fq.form_id OR p.post_survey_form_id = fq.form_id
    LEFT OUTER JOIN dbo.survey_question sq
        ON fq.question_id = sq.question_id
    LEFT OUTER JOIN dbo.question_option qo 
        ON sq.question_id = qo.question_id
    LEFT OUTER 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 p.program_id, fq.form_id, sq.question_id, qo.question_option_id

I need to join them on the rows where question_id matches. Please help.

Ok, I tried using a CTE and did not have any luck. I was able to create one CTE but it would not let me do two. I still did not know how to implement the JOIN this way. How might I do this with table variables? — Darren, Apr 10 '12 at 15:53

Mike Ryan · Accepted Answer · 2012-04-10T17:23:41.650

The only additional piece of information in the 1st query that's missing from the second is

COUNT(ra.question_option_id) AS TotalAnswers

on the dbo.form_response_answers ra table.

So, just add this count into your select:

    (select count(*) from dbo.form_response_answers ra
         where qo.question_option_id = ra.question_option_id) as AS TotalAnswers

as in:

SELECT  p.program_id, 
        p.pre_survey_form_id, 
        p.post_survey_form_id, 
        fq.form_id, 
        sq.question_id, 
        sq.question_text, 
        qo.question_option_id, 
        qo.option_text, 
        G.Total,
        (select count(*) from dbo.form_response_answers ra
         where qo.question_option_id = ra.question_option_id) as AS TotalAnswers


FROM    dbo.program p
    LEFT OUTER JOIN dbo.form_question fq
    ON p.pre_survey_form_id = fq.form_id OR p.post_survey_form_id = fq.form_id
LEFT OUTER JOIN dbo.survey_question sq
    ON fq.question_id = sq.question_id
LEFT OUTER JOIN dbo.question_option qo 
    ON sq.question_id = qo.question_id
LEFT OUTER 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 p.program_id, fq.form_id, sq.question_id, qo.question_option_id

EDIT: You wanted the total # of Answers for each sq.question_id.

So, I should have inserted:

(select count(ra2.question_option_id) 
   from dbo.question_option qo2
   LEFT OUTER JOIN dbo.form_response_answers ra2
       ON qo2.question_option_id = ra2.question_option_id
   where qo2.question_id = sq.question_id) as TotalAnswers

Now, of course, that will be repeated multiple times because there are more rows in Query 2 than Query 1.

This does not work because it loses the grouping that I have in place in the first query and therefore the count returns different results. — Darren, Apr 10 '12 at 15:41
That is actually desirable that it is repeated because I will be using that column to calculate the percent for the number of times a particular response was chosen. — Darren, Apr 10 '12 at 19:47

Ricibob · Answer 2 · 2012-04-10T16:18:12.160

Im no expert but wont:

SELECT  sq.question_id,     
    COUNT(ra.question_option_id) AS TotalAnswers INTO [#temp_table1]
...

SELECT  p.program_id, 
    p.pre_survey_form_id, 
    p.post_survey_form_id, 
    fq.form_id, 
    sq.question_id, 
    sq.question_text, 
    qo.question_option_id, 
    qo.option_text, 
    G.Total
INTO [#temp_table2]
...

then:

SELECT * FROM [#temp_table1] JOIN ON [#temp_table1].question_id = [#temp_table2].question_id

get the job done?

How to perform a OUTER JOIN on two complex SQL queries?

2 Answers2