how to select a record from one table and count the number of which appears another table and count it

debugcn Published at Dev

sureone

I have below tow table table 1: The teacher table

teacher_id   teacher_name
1            xx
2            yy
3            zz

table 2: the student table

stu_id       stu_name     tearcher1_id     teacher2_id    tearcher3_id
1            aa           1                2 
2            bb           2                3
3            cc           1

I want to get a list by one sql statement from the teachers included the count of who appears in student table as below:

teacher_id      teacher_name          num_selected_by_stu
1               xx                    2
2               yy                    2
3               zz                    1

I have tried below sql but seems not work,

    select * from teatcher t1 
    left join (
    select stu_id,tearcher1_id,tearcher2_id,tearcher3_id,count(stu_id) as num_selected_by_stu from student 
    group by stu_id,tearcher1_id,tearcher2_id,tearcher3_id) t2 
    ON ( t2.teacher1_id=t1.teacher_id or t2.teacher2_id=t1.teacher_id or t2.teacher3_id=t1.teacher_id)

and,anyone can help?

schurik

SELECT 
  teacher_id
  teacher_name
  NVL(num1, 0) + NVL(num2, 0) + NVL(num3,0) as num_selected_by_stu
FROM 
  teacher t
  left outer join ( SELECT count(*) as num1, tearcher1_id FROM student group by tearcher1_id ) t1 on t1.tearcher1_id = t.tearcher_id
  left outer join ( SELECT count(*) as num2, tearcher2_id FROM student group by tearcher2_id ) t2 on t2.tearcher2_id = t.tearcher_id
  left outer join ( SELECT count(*) as num3, tearcher3_id FROM student group by tearcher3_id ) t3 on t3.tearcher3_id = t.tearcher_id
;

Collected from the Internet

Please contact [email protected] to delete if infringement.