kjstan kjstan - 3 months ago 8
SQL Question

No rows are selected if table in join doesn't have row

I'm writing a query to select information about all the questions on a quiz (ModuleId). I need the most recent user answer, along with the question, the answer key, and the grader information, if there is any.

CREATE PROCEDURE spGetQuestionsAnswersMostRecentUserAnswersAndRevisions(@UserId char(7),@ModuleId int) AS
SELECT ua.QuestionId, ua.UserAnswerId, UserAnswer, Question, Answer, TypeId, GraderRevision, IsAnswerCorrect
FROM UserAnswersByModule ua
INNER JOIN QuestionsAnswersByModule qa
ON qa.QuestionId = ua.QuestionId
INNER JOIN GradedAnswersByQuestion ga
ON ga.UserAnswerId = ua.UserAnswerId
WHERE ua.UserAnswerId
IN (
SELECT MAX(UserAnswerId) AS MostRecentUserAnswer FROM UserAnswersByModule WHERE ModuleId = @ModuleId AND UserId = @UserId AND IsActive = 1 GROUP BY QuestionId
)
AND ga.RevisionId IN (
SELECT MAX(RevisionId) AS MostRecentRevisionId
FROM GradedAnswersByQuestion GA
INNER JOIN UserAnswersByModule
ON ga.UserAnswerId = ua.UserAnswerId
WHERE UserId = @UserId
AND ModuleId = @ModuleId
GROUP BY GA.UserAnswerId
)


This is how the tables are set up:

QuestionAnswersByModule
PK - QuestionId

UserAnswersByModule
PK - UserAnswerId
FK - QuestionAnswersByModule.QuestionId

GradedAnswersByQuestion
PK - RevisionId
FK - UserAnswersByModule.UserAnswerId


The problem with my query is that, if there isn't anything in GradedAnswersByQuestion for the particular UserAnswerId, the query returns nothing at all, instead of null for those two fields and the rest of the information. I'm pretty sure it's because of the AND ga.RevisionId IN ... but I can't think of how else to write it. Any help would be appreciated.

Answer

Perhaps this does the trick with minimal changes...

CREATE PROCEDURE spGetQuestionsAnswersMostRecentUserAnswersAndRevisions(@UserId char(7),@ModuleId int) AS
SELECT ua.QuestionId, ua.UserAnswerId, UserAnswer, Question, Answer, TypeId, GraderRevision, IsAnswerCorrect 
FROM UserAnswersByModule ua
INNER JOIN QuestionsAnswersByModule qa
ON qa.QuestionId = ua.QuestionId
LEFT OUTER JOIN GradedAnswersByQuestion ga /* <-- Changed! */
ON ga.UserAnswerId = ua.UserAnswerId
WHERE ua.UserAnswerId 
IN (
  SELECT MAX(UserAnswerId) AS MostRecentUserAnswer FROM UserAnswersByModule WHERE ModuleId = @ModuleId AND UserId = @UserId AND IsActive = 1 GROUP BY QuestionId
)
AND (
  ga.RevisionId is null /* <-- Changed! */
  OR ga.RevisionId IN (
    SELECT MAX(RevisionId) AS MostRecentRevisionId 
    FROM GradedAnswersByQuestion GA
    INNER JOIN UserAnswersByModule
    ON ga.UserAnswerId = ua.UserAnswerId
    WHERE UserId = @UserId
    AND ModuleId = @ModuleId
    GROUP BY GA.UserAnswerId
  )
)
Comments