I'm trying to force through zero values for grades that don't exist in my Results table, but do exist in my list of possible Grades table.
I've managed to join tables successfully in order to almost achieve this using this previous post as guidance up to a point.
As you can see from my fiddle the resultset is displaying NULL values for my Year and Subject columns and I would like these to display the relevant subject.
Don't use * in SELECT instead use specify the colums you need and use ISNULL and make it as zero like:
SELECT ISNULL(t.amount,0)
FROM [yourtable] y
left join [someOtherTable] t
ON y.id=t.id
What you need is a table of Subjects and Years, then cross join for grades...
create table Subjects(SubjectID INT, Subject Varchar(50));
create table YearSub (SubjectID INT, Year INT, CrateDate Date); -- Map your available subjects for each year in here
Once you have thiese, make a CTE to hold the full list of available subjects, years and grades
with AllGrades as
(select SubjectID, Year, Grade
from YearSub YS
cross join Grades
)
select ...
from AllGrades
left join ...
And so on
i think this is what you wanted.
first you get all combinition of student & grades by using CROSS JOIN
then you LEFT JOIN to the resutl table to the get the count
select s.year, subject grade, grade, count(wag) as Total
from student s
cross join grades g
left join results r on s.upn = r.upn and s.upn = r.upn
group by s.year, g.grade
