lundi 22 juin 2015

SQL join to add missing rows

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

Aucun commentaire:

Enregistrer un commentaire