Displaying Row In Resultset Where No Values Exist

Question

I've read a couple of questions on here that seem to answer this e.g. Return a value if no rows are found SQL

However, I'm struggling to get it to work for me. This is the output of my query as it stands:

As an example there are actually six classes, but only three display because they actually have results. I would like the classes without results to display too with null values in the No. Other column.

Here's my code so far:

DECLARE @AcademicYear varchar(9) = '2017/2018',
        @Collection varchar(50) = 'Autumn';


SELECT

Test = NULLIF(COUNT(g1.Points),0),

 cast(s.Year as int) as Year,

CASE r.Subject
    WHEN 'English' THEN 1
    WHEN 'English Language' THEN 2
    WHEN 'English Literature' THEN 3
    WHEN 'Maths' THEN 4
    WHEN 'Science' THEN 5
    WHEN 'Additional Science' THEN 6
    WHEN 'Biology' THEN 7
    WHEN 'Chemistry' THEN 8
    WHEN 'Physics' THEN 9
    WHEN 'Arabic' THEN 10
    WHEN 'Dutch' THEN 11
    WHEN 'French' THEN 12
    WHEN 'Russian' THEN 13
    WHEN 'Spanish' THEN 14
    WHEN 'Urdu' THEN 15
    ELSE 16
  END AS SubjectSort,

 r.Subject, r.Class, 
  0 AS GroupSort,
'SEND (' + CAST(COUNT(g1.Points) AS varchar) + ')' AS 'Group',


    --Other
 SUM(CASE
    WHEN r.Progress in ('X','Abs','New') THEN 1
    ELSE 0
  END) AS 'No. Other'

FROM Results r
 JOIN Grades g1
  ON r.Result = g1.Grade
LEFT JOIN students s
  ON r.UPN = s.UPN

WHERE r.AcademicYear = @AcademicYear
AND s.AcademicYear = @AcademicYear
AND r.Collection = @Collection
AND SEND = 'Y'

GROUP BY s.Year,
         r.Subject, r.Class

Order by cast(s.year as int) desc, SubjectSort, r.Subject, r.Class, GroupSort

Have you tried using a left join instead of inner joins? You should get in the habit of referencing your columns with the alias so it is clear what table a given column resides in. In this query we can't tell what column is from what table because there are no aliases and no table definitions. — Sean Lange, Jan 09 '18 at 21:46
Which of the three tables in your query have rows/data for the missing subjects? Or is the subject list in another table (if so what table)? Can you show your schema and some sample data? — Dave C, Jan 09 '18 at 21:52
It's the results table, sorry that wasn't clear. I'll update with schema and data. — Matt, Jan 09 '18 at 22:00
You need to move the predicate s.AcademicYear = @AcademicYear to the join. When you have it in the where clause it turns your left join to an inner join. And what table is SEND coming from? That could be the same issue. — Sean Lange, Jan 09 '18 at 22:01

fauxmosapien · Answer 1 · 2018-01-09T22:08:40.727

0

When you use JOIN it is assumed it is an inner join, and the inner join is excluding results that don't have items in the grade table. Just make it a left join:

DECLARE @AcademicYear varchar(9) = '2017/2018',
        @Collection varchar(50) = 'Autumn';


SELECT

Test = NULLIF(COUNT(g1.Points),0),

 cast(Year as int) as Year,

CASE Subject
    WHEN 'English' THEN 1
    WHEN 'English Language' THEN 2
    WHEN 'English Literature' THEN 3
    WHEN 'Maths' THEN 4
    WHEN 'Science' THEN 5
    WHEN 'Additional Science' THEN 6
    WHEN 'Biology' THEN 7
    WHEN 'Chemistry' THEN 8
    WHEN 'Physics' THEN 9
    WHEN 'Arabic' THEN 10
    WHEN 'Dutch' THEN 11
    WHEN 'French' THEN 12
    WHEN 'Russian' THEN 13
    WHEN 'Spanish' THEN 14
    WHEN 'Urdu' THEN 15
    ELSE 16
  END AS SubjectSort,

  Subject, Class, 
  0 AS GroupSort,
'SEND (' + CAST(COUNT(g1.Points) AS varchar) + ')' AS 'Group',


    --Other
 SUM(CASE
    WHEN Progress in ('X','Abs','New') THEN 1
    ELSE 0
  END) AS 'No. Other'

FROM Results r
LEFT JOIN Grades g1
  ON r.Result = g1.Grade
LEFT JOIN students s
  ON r.UPN = s.UPN AND s.SEND = 'Y' AND s.AcademicYear = @AcademicYear

WHERE r.AcademicYear = @AcademicYear
    AND r.Collection = @Collection


GROUP BY Year,
         Subject, Class

Order by cast(year as int) desc, SubjectSort, Subject, Class, GroupSort

edited Jan 09 '18 at 22:08

answered Jan 09 '18 at 21:47

fauxmosapien

525
3
6

thanks for this. However I still get the same output as before. Any other ideas? – Matt Jan 09 '18 at 21:53
Which table are Subject and Class coming from? I had assumed Results. – fauxmosapien Jan 09 '18 at 21:55
I've updated the columns with their aliases. Hopefully that might help, but you're correct that's where they are. – Matt Jan 09 '18 at 21:59
Which table holds the field SEND? And is it safe to assume that there will be a student record even if there is no result record? – fauxmosapien Jan 09 '18 at 22:04
SEND is in Students and yes a student won't necessarily have a result for all subjects. – Matt Jan 09 '18 at 22:05
Does that mean that some classes in Results won't have a student in Students? If so you need to move the items in the WHERE clause relating to students into the join in order to keep classes with no students. I've edited my answer to do this. – fauxmosapien Jan 09 '18 at 22:09

Displaying Row In Resultset Where No Values Exist

1 Answers1