CODEWITHSUNDEEP
sql-serversql-server-2012
SkyeBoniwell
SkyeBoniwell

Reputation: 7092

Listing all desired records from a table even if the joined table contains NULLs

Get ALL questions in the QuizQuestions table for a specific quiz.

Get ALL students in a particular class from the ClassStudents table.

Get ALL student responses from the specified class, for a particular quiz from the StudentAnswers table. Only stores records of questions that have been answered on the quiz by a student.

So I wrote this:

SELECT cs.lastName,  qq.questionStem, ISNULL(sa.selectedAnswer, 'Did not answer') AS Answer
FROM StudentAnswers sa
right join QuizQuestions qq ON qq.questionId = sa.questionId
right join ClassStudents cs ON sa.studentID = cs.studentID
WHERE qq.quizID = 12345
AND cs.classID = 500
ORDER BY lastName

It does get the data, but if a student hasn't answered a particular question or taken the quiz, I just get their lastName but NULLs for questionStem.

But I want to see the questionStem for the question they didn't answer.

Looking at my query, I can see why I get a NULL. It's because there is not an entry in the StudentAnswers table if the student never answered the question.

Is there a way to re-write the query so that every question stem is displayed, even if the student never answered it?

I'm trying to get results like this:

lastName            questionStem            Answer
--------------------------------------------------------
Smith               Favorite color?         Black
Jones               Favorite color?         Did not answer
Aki                 Favorite color?         Red
Smith               Your age?               22
Jones               Your age?               71
Aki                 Your age?               Did not answer

Upvotes: 0

Views: 27

Answers (1)

S4V1N
S4V1N

Reputation: 279

Try this 

SELECT cs.lastName,  qq.questionStem, ISNULL(sa.selectedAnswer, 'Did not answer') AS Answer
FROM StudentAnswers sa
FULL JOIN QuizQuestions qq ON qq.questionId = sa.questionId
FULL JOIN ClassStudents cs ON cs.studentID = sa.studentID  
WHERE qq.quizID = 12345
AND cs.classID = 500
ORDER BY lastName

Reference: SQL Server FULL Joins

Upvotes: 2

Related Questions