CODEWITHSUNDEEP
sqlsql-server
nikotromus
nikotromus

Reputation: 1054

SQL Server left join not returning blank entry

I am trying to fetch all records from the question table, whether or not they exist in the answer table. There are 50 questions all together, but I am only retrieving 49. What am I doing wrong?

select 
    q.question_txt 
from 
    apdev.form_template.question q 
left join 
    apdev.form_submission.answer a on q.question_id = a.question_id
where 
    a.submission_id = 2704336
    and q.form_id = 23

Upvotes: 0

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Classic error. Condition on all but the first table need to go in the on clause:

select q.question_txt 
from apdev.form_template.question q left join
     apdev.form_submission.answer a
     on q.question_id = a.question_id and a.submission_id = 2704336
where q.form_id = 23;

Why? The left join produces a NULL value for submission_id in non-matching rows. The condition a.submission_id = 2704336 fails. Although you could add (a.submission_id = 2704336 or a.submission_id is null), that doesn't have the same semantics. It is a good exercise for you to figure out why that doesn't solve the problem.

Upvotes: 6

Related Questions