Select null on a join where a record doesn't exist in another table

Question

I've got a problem that I can't seem to figure out after a bunch of failed attempts.

I've got three tables that I need to do a join on for some reporting, and in the 2nd table a record might not exist. If the record doesn't exist, I need to report a null value for the data that comes from the 3rd table.

In the most basic form, here are the table structures (it's for a survey)

Table A (Survey)
-----------------------
SurveyNumber | SurveyId
016243023708   1152010

Table B (Response)
----------------------------------
SurveyId | QuestionId | ResponseId
1152010    1279235      486

Table C (Response Values)
--------------------
ResponseId | Value
486          Yes

To explain why a record may not exist in Table B is simply because the values are inserted as the survey is completed. If the user leaves the survey without finishing (they can come back later), the record in Table B won't be there. The value in table C should be reported as null for this.

If it makes it any easier, I need to do the reporting specifically for questionid 1279235.

This is the query I've come up with so far (it shows me everything but the surveys with a missing record in table b for question 1279235).

SELECT      A.SurveyNumber, A.SurveyId, B.QuestionID, C.Value
FROM        tblA A
LEFT JOIN   tblB B
        ON  A.SurveyId = B.SurveyId
LEFT JOIN   tblC C
        ON  B.ResponseId = C.ResponseId 
WHERE       B.QuestionId = 1279235

I can provide more clarification if it is needed.

Thanks in advance

Answer 1

do not put the condition in the where clause, but instead on the join part since records on tablec may not exist.

SELECT      A.SurveyNumber, A.SurveyId, B.QuestionID, C.Value
FROM        tblA A
            LEFT JOIN   tblB B
                  ON  A.SurveyNumber = B.SurveyNumber AND 
                      B.QuestionId = 1279235
            LEFT JOIN   tblC C
                  ON  B.ResponseId = C.ResponseId

Answer 2

try this:

SELECT
    A.SurveyNumber, A.SurveyId, B.QuestionID, C.Value
    FROM tblA        A
    LEFT JOIN tblB   B ON  A.SurveyId=B.SurveyId AND B.QuestionId=1279235
    LEFT JOIN tblC   C ON  B.ResponseId=C.ResponseId 

EDIT working example:

DECLARE @tblA  table (SurveyNumber varchar(12),SurveyId int)
INSERT INTO @tblA VALUES ('016243023708',   1152010)
INSERT INTO @tblA VALUES ('016243023708',   1152011)

DECLARE @tblB table (SurveyId int, QuestionId int, ResponseId int)
INSERT INTO @tblB values (1152010,    1279235,      486)
INSERT INTO @tblB values (1152011,    1279235,      487)

DECLARE @tblC  table (ResponseId int, ValueOf varchar(10))
INSERT INTO @tblC values (486,          'Yes')


SELECT
    A.SurveyNumber, A.SurveyId, B.QuestionID, C.ValueOf
    FROM @tblA        A
    LEFT JOIN @tblB   B ON  A.SurveyId=B.SurveyId AND B.QuestionId=1279235
    LEFT JOIN @tblC   C ON  B.ResponseId=C.ResponseId 

OUTPUT:

SurveyNumber SurveyId    QuestionID  ValueOf
------------ ----------- ----------- ----------
016243023708 1152010     1279235     Yes
016243023708 1152011     1279235     NULL

(2 row(s) affected)