SQL join to add missing rows

Question

I am new to SQL and need some help. In a scenario I want to join on Teachers Table where I have Data of subject a teacher taught. I want to add some missing rows through join so that I can show the rows where a class is being taught in a year but missing its teachers details. Either in any term for all subjects.

Note there could be multiple no. Of terms in a year

Mapping Table Of teachers and Class they thought

Teacher Class
Aman    9th
Ankit   9th
Abhinav 10th
Bharat  10th

Mapping Table Of teachers and term in which they thought

Teacher Term
Aman    1stTerm
Ankit   2nd Term
Abhinav 2nd Term
Bharat  1stTerm

Data Table

Subject YEAR    Teacher Marks
Maths   2014    Aman    80
Maths   2014    Ankit   85
Maths   2015    Abhinav 69
Science 2014    Abhinav 30
Science 2015    Aman    20

Output Table

Subject Class   Teacher Class   Term        Marks
Maths   2014    Aman    9th     1stTerm     80
Maths   2014    Ankit   9th     2nd Term    85
Maths   2015    Abhinav 10th    2nd Term    69
Maths   2015    Bharat  10th    1stTerm     0
Science 2014    Abhinav 10th    2nd Term    30
Science 2014    Bharat  10th    1stTerm     0
Science 2015    Aman    9th     1stTerm     20
Science 2015    Ankit   9th     2nd Term    0

Answer 1

I can not check at the moment but something like this should work:

select *, coalesce(dt.Marks, 0) as Marks
from TeacherClass tc
join TeacherTerm tt on tc.Teacher = tt.Teacher
cross join (select 2014 as Year union select 2015 as Year) cj
left join DataTable dt on tc.Teacher = dt.Teacher and cj.Year = dt.Year



DECLARE @TeacherClass TABLE(Teacher VARCHAR(MAX), Class VARCHAR(MAX))
DECLARE @TeacherTerm TABLE(Teacher VARCHAR(MAX), Term VARCHAR(MAX))
DECLARE @DataTable TABLE(Subject VARCHAR(MAX), YEAR  INT,   Teacher VARCHAR(MAX), Marks INT)


INSERT INTO @TeacherClass VALUES
('Aman','9th'),
('Ankit','9th'),
('Abhinav','10th'),
('Bharat','10th')

INSERT INTO @TeacherTerm VALUES
('Aman','1stTerm'),
('Ankit','2nd Term'),
('Abhinav','2nd Term'),
('Bharat','1stTerm')

INSERT INTO @DataTable VALUES
('Maths',   2014    ,'Aman',    80),
('Maths',   2014    ,'Ankit',   85),
('Maths',   2015    ,'Abhinav', 69),
('Science', 2014    ,'Abhinav', 30),
('Science', 2015    ,'Aman',    20)


select Subject, cj.Year, tc.Teacher, tc.Class, tt.Term, coalesce(dt.Marks, 0) as Marks
from @TeacherClass tc
join @TeacherTerm tt on tc.Teacher = tt.Teacher
cross join (select 2014 as Year union select 2015 as Year) cj
left join @DataTable dt on tc.Teacher = dt.Teacher and cj.Year = dt.Year
ORDER BY Subject, cj.YEAR

Output:

Subject Year    Teacher Class   Term        Marks
NULL    2014    Bharat  10th    1stTerm     0
NULL    2015    Bharat  10th    1stTerm     0
NULL    2015    Ankit   9th     2nd Term    0
Maths   2014    Aman    9th     1stTerm     80
Maths   2014    Ankit   9th     2nd Term    85
Maths   2015    Abhinav 10th    2nd Term    69
Science 2014    Abhinav 10th    2nd Term    30
Science 2015    Aman    9th     1stTerm     20