多表分类查询
人员表teacher字段为name，lessonId，position；职称认证课程表attestation字段为position，lessonId，lessonPassScore；认证课程考试结果表result字段为lessonId，score当人员的职位所对应的的所有课程都通过时才能得到认证（一个name只有一个position但一个position有多个lessonId需要通过）。怎样用sql实现？
oracle查询
[解决办法]
大致思路是
1、先求出有课程不通过的人员
2、然后NOT IN
[解决办法]

SQL> select * from teacher order by 1;

NAME         LESSONID POSITION
---------- ---------- ---------------
jack                1 professor
jack                3 professor
jack                2 professor
rose                2 vice_professor
rose                1 vice_professor
tom                 3 professor
tom                 2 professor
tom                 1 professor

8 rows selected.

SQL> select * from attestation order by 1;

POSITION          LESSONID LESSONPASSSCORE
--------------- ---------- ---------------
professor                3              60
professor                2              80
professor                1              80
vice_professor           1              70
vice_professor           2              70

SQL> select * from result order by 1;

NAME         LESSONID      SCORE
---------- ---------- ----------
jack                1        100 
 
jack                3         10
jack                2        100
rose                2        100
rose                1        100
tom                 3        100
tom                 2        100
tom                 1        100

8 rows selected.

SQL> select distinct name from teacher where name not in
  2  (
  3          select result.name from 
  4     (
  5             select t.*,a.lessonpassscore from teacher t,attestation a 
  6             where 
  7             t.lessonid = a.lessonid and t.position = a.position
  8          ) 
  9     temp,result 
 10     where 
 11     temp.name = result.name and temp.lessonid = result.lessonid and result.score < temp.lessonpassscore
 12  );

NAME
----------
tom
rose

SQL>