CODEWITHSUNDEEP
sqljoinnull
user3337629
user3337629

Reputation: 189

Left join not returning record with null

Here's the structure of my database (used to collect info from a survey):

question_responses->answer_choices->subquestions->questions->survey
    |                                                         ^
    V                                                         |
submissions->response_group<----------------------------survey_deployment

where A->B means A has the PK of B

So, I want to get the responses of a particular user; let's say that his submission pk is 1. I had written the query

select  question_responses.answer_text
from    submissions      join question_responses on (question_responses.submission_pk = submission.pk)
                         join answer_choices     on (question_responses.answer_choices_pk = answer_choices.pk)
                         join subquestions       on (answer_choices.subquestions_pk = subquestions.pk)
                   right join questions          on (subquestions.questions_pk = questions.pk)
where       submissions.pk = 1
order by    questions.order

It is possible to not answer questions. In such a case, there is no question_responses.pk recorded. I would still like to be able to account for skipping, so I need this record to be returned, even if there is no response. I thought that the right join would have accounted for this record, but apparently not.

Any help is greatly appreciated.

Upvotes: 0

Views: 57

Answers (2)

GarethD
GarethD

Reputation: 69819

Because you have submissions.pk = 1 in the where clause this effectively turns your outer join into an inner join, since any missing submission will have a pk of null. and null = 1 is not true.

You could rewrite your query using a LEFT JOIN, and selecting from questions:

select  question_responses.answer_text
from    questions
        left join (subquestions
            inner join answer_choices
                on answer_choices.subquestions_pk = subquestions.pk
            inner join question_responses
                on question_responses.answer_choices_pk = answer_choices.pk
            inner join submissions
                on question_responses.submission_pk = submission.pk)
            on subquestions.questions_pk = questions.pk
            and submissions.pk = 1
order by    questions.order;

This is similar to doing something like:

select  subquery.answer_text
from    questions
        left join 
        (   select question_responses.answer_text, subquestions.questions_pk
            from subquestions
                inner join answer_choices
                    on answer_choices.subquestions_pk = subquestions.pk
                inner join question_responses
                    on question_responses.answer_choices_pk = answer_choices.pk
                inner join submissions
                    on question_responses.submission_pk = submission.pk
                where submissions.pk = 1
        ) subquery
            on subquery.questions_pk = questions.pk
order by    questions.order;

but depending on your dbms the former may perform better as it has no derived tables.

Upvotes: 1

TechnoBrat
TechnoBrat

Reputation: 277

Check out this, does it help?

SELECT  question_responses.answer_text
FROM    questions LEFT JOIN subquestions on (subquestions.questions_pk = questions.pk)
                  LEFT JOIN answer_choices on (answer_choices.subquestions_pk = subquestions.pk)
                  LEFT JOIN question_responses on (question_responses.answer_choices_pk = answer_choices.pk)
                  LEFT JOIN submissions on (question_responses.submission_pk = submission.pk)
WHERE submissions.pk = 1
ORDER BY  questions.order

Upvotes: 0

Related Questions