sql - 在另一个表中不存在记录的联接上选择 null

Question

我遇到了一个问题，经过多次失败的尝试后我似乎无法解决。

我需要对三个表进行联接以进行某些报告，而第二个表中的记录可能不存在。如果记录不存在，我需要报告来自第三个表的数据为空值。

在最基本的形式中，以下是表格结构(用于调查)

Table A (Survey)-----------------------SurveyNumber | SurveyId016243023708   1152010Table B (Response)----------------------------------SurveyId | QuestionId | ResponseId1152010    1279235      486Table C (Response Values)--------------------ResponseId | Value486          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

如果需要，我可以提供更多说明。

提前致谢

Answer 1

不要将条件放在where子句中，而是放在连接部分，因为tablec上的记录可能不存在。

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