I have 3 table missing data find in SQL

Question

enter image description here

select * from TimeSheet
WeekID  StudentID   Hours
1        1           40
2        1           40
4        1           40
1        2           40
2        2           40
3        2           4
4        2           40
1        4           40
3        3           40

select * from Week
WeekID
1
2
3
4

select * from Student
StudentID   StudentName
1             ABC
2             XYZ
3             ASD
4             QWE

After the join I have these results.

If have this data using three tables

select TS.WeekID
     , TS.StudentID
     , TS.[Hours]
     , SS.StudentID
     , WW.WeekID
FROM TimeSheet TS
left JOIN Student SS ON SS.StudentID = TS.StudentID
left JOIN [Week] WW ON WW.WeekID = TS.WeekID 

 WeekID StudentID   Hours   StudentID   WeekID
  1      1          40        1          1
  2      1          40        1          2
  4      1          40        1          4
  1      2          40        2          1
  2      2          40        2          2
  3      2          4         2          3
  4      2          40        2          4
  3      3          40        3          3
  1      4          40        4          1

Expectation Result is ...

WeekID  StudentID   Hours   StudentID   WeekID

3        1          40          1       2
1        3          40          3       3
2        3          40          3       3
4        3          40          3       3
2        4          40          4       1
3        4          40          4       1
4        4          40          4       1

I want to get for example:

• student 1 and week 3 is missing
• student 2 no week is missing
• and student 3 1,2 and 4 week is missing
• and student 4 2,3,4 week is missing.

Please help me out.

Answer 1

If you cross join your Week and Student tables to get all combinations, and then use not exists to determine where no TimeSheet record exists.

select W.WeekID, S.StudentID
from [Week] W
cross join Student S
where not exists (select 1 from TimeSheet T where T.StudentID = S.StudentID and T.WeekID = W.WeekID);