SELECT using multiple JOINS needs to return even if one compairson is not equal

Question

I have been working on a query:

SELECT P.[Name]+' - '+P.[Description] AS Sprint, S.[Number] AS Story, T.[Name] AS Task
FROM DailyTaskHours D
INNER JOIN Task T ON D.TaskId = T.PK_Task 
INNER JOIN Story S ON T.StoryId = S.PK_Story 
INNER JOIN Sprint P ON S.SprintId = P.PK_Sprint 
GROUP BY  P.[Name], P.[Description], S.[Number], T.[Name]

The Sprint column may or may not be NULL:

enter image description here

The above query will only return the requested columns if there is a SprintId associated. If it is NULL the whole column will not be returned. This makes sense, S.SprintId = P.PK_Sprint is not equivelant when it is NULL on the Story table.

If it's NULL I still want it to return the row with all of the other tables columns data but with the word KanBan instead of not returning anything. How do I achieve this?

`INNER JOIN` requires records to be present on BOTH sides of the join. You want a LEFT join, which requires records present on the 'left' side of the join, any any missing records on the 'right' side will simply be null. — Marc B, Sep 17 '13 at 14:03

score 2 · Accepted Answer · answered Sep 17 '13 at 14:01

Change S.Number to ISNULL(S.Number, 'KanBan'). This will add 'Kanban' in case no matching sprintID is found in the Sprint table.

Change INNER JOIN Sprint P ON S.SprintId = P.PK_Sprint to LEFT JOIN Sprint P ON S.SprintId = P.PK_Sprint. This ensures that all other records will still show, even in case of no match.

The full query then becomes:

SELECT P.[Name]+' - '+P.[Description] AS Sprint, ISNULL(S.Number, 'KanBan') AS Story,     T.[Name] AS Task
FROM DailyTaskHours D
INNER JOIN Task T ON D.TaskId = T.PK_Task 
INNER JOIN Story S ON T.StoryId = S.PK_Story 
LEFT JOIN Sprint P ON S.SprintId = P.PK_Sprint 
GROUP BY  P.[Name], P.[Description], S.[Number], T.[Name]

score 2 · Answer 2 · answered Sep 17 '13 at 14:01

SELECT ISNULL(P.[Name]+' - '+P.[Description],'KanBan') AS Sprint, S.[Number] AS Story, T.[Name] AS Task
FROM DailyTaskHours D
INNER JOIN Task T ON D.TaskId = T.PK_Task 
INNER JOIN Story S ON T.StoryId = S.PK_Story 
LEFT OUTER JOIN Sprint P ON S.SprintId = P.PK_Sprint 
GROUP BY  P.[Name], P.[Description], S.[Number], T.[Name]

score 1 · Answer 3 · edited May 23 '17 at 11:53

Try this

  SELECT P.[Name]+' - '+P.[Description] AS Sprint, S.[Number] AS Story, T.[Name] AS Task
    FROM DailyTaskHours D
    INNER JOIN Task T ON D.TaskId = T.PK_Task 
    INNER JOIN Story S ON T.StoryId = S.PK_Story 
    LEFT OUTER JOIN Sprint P ON S.SprintId = P.PK_Sprint 
    GROUP BY  P.[Name], P.[Description], S.[Number], T.[Name]

Please have a look Difference between inner and outer join

SELECT using multiple JOINS needs to return even if one compairson is not equal

3 Answers3