I would like to calculat of distant related values, can someone please tell me how to do it?
This is my scenario and the table
3 main table
School
| School_cd | School_name |
| 100 | School0 |
| 101 | School1 |
| 102 | School2 |
| 103 | School3 |
Student
HW: If homework is done 1, if not 0
| student_cd | student_name | HW |
| 300 | student_0 | 1 |
| 301 | student_1 | 1 |
| 302 | student_2 | 1 |
| 303 | student_3 | 0 |
| 304 | student_4 | 0 |
| 305 | student_5 | 1 |
manager
| manager_cd | manager_name |
| 200 | ｍanager0 |
| 201 | ｍanager1 |
In addition to the above, there are two other tables.
School_manager
| school_cd | Area | manager_cd |
| 100 | A | 200 |
| 101 | A | 200 |
| 102 | B | 200 |
| 103 | B | 201 |
School_Student
The student belongs to only one main school, and the main school is flagged 1 for "main_school_flag".
| school_cd | student_cd | main_school_flag |
| 100 | 300 | 1 |
| 100 | 301 | 1 |
| 101 | 300 | 0 |
| 101 | 302 | 1 |
| 102 | 300 | 0 |
| 102 | 303 | 1 |
| 102 | 304 | 1 |
| 103 | 305 | 1 |
The result I want to get is the number of students in each manager who are doing and not doing their homework.
Students attend more than one school and I would like to count them in the main school.
Since a manager may be responsible for more than one area, we also output the areas.
| Area | manager_cd | HW done | not yet | school |
| A | 200 | 3 | | 100,101 |
| B | 200 | | 2 | 102 |
| B | 201 | 1 | | 103 |
