解决方案需要 3 个表的 SQL 查询

Question

我需要一些帮助来进行查询。我被困住了，有点失去了继续我的项目的动力。

我有 3 个表:

Create Table teacher (bsn char(11) Primary Key,
                     first_name varchar(12),
                     surname varchar(30),
                     scale int,
                     salary real,
                     Check (scale >= 9 and scale <= 13),
                     Check (salary >= 20*scale and salary <= 35*scale)
                     );

Create Table teaches (teacherbsn char(11) References teacher(bsn),
                     coursecode char(11) References course(code),
                     studentid char(7) References student(id),
                     Primary Key (teacherbsn, coursecode, studentid));

Create Table workson (teacherbsn char(11) References teacher(bsn),
                     coursecode char(11),
                     assignmentcode char(13),
                     role_of_teacher teacherrole,
                     Primary Key (teacherbsn, coursecode, assignmentcode),
                     Foreign Key (coursecode, assignmentcode) References 
                     assignment(coursecode, assignmentcode));

我需要找到教授类(class)但不做作业的老师 (workson)

这是我的想法:

SELECT teacher.first_name FROM teacher, (SELECT teaches.teacherbsn
FROM teaches RIGHT JOIN workson ON teaches.teacherbsn = workson.teacherbsn) as t
WHERE t.teacherbsn = NULL
ORDER BY teacher.first_name

以这种方式，连接应该过滤所有存在的 teacherbsn，所以我所要做的就是 SELECT WHERE table.column_name = NULL，但这不起作用......

答案应该是这样的:

|first_name | teacherbsn(workson)|
+-----------+--------------------+
|Tommie     | NULL               |

如果您需要更多信息，我很乐意为您提供!

这是我正在处理的数据:

  Insert Into teacher Values
   ('52269-69987', 'Claudius', 'Streather', '10', '200'),
   ('59614-58753', 'Dona', 'Milbank', '12', '380'),
   ('25856-40101', 'Riley', 'Pugsley', '12', '272'),
   ('98208-93540', 'Garwood', 'Hattrick', '11', '220'),
   ('64756-68937', 'Juieta', 'Kunzelmann', '11', '354'),
   ('61092-02471', 'Sallyanne', 'Delort', '13', '350'),
   ('94203-26749', 'Leann', 'Kleinhaus', '11', '220'),
   ('42374-12317', 'Leigha', 'Cianelli', '11', '220'),
   ('83265-24378', 'Karel', 'Eisak', '11', '246'),
   ('78910-58311', 'Tomi', 'Lowis', '10', '350');

Insert Into teaches Values
   ('52269-69987', 'CRS01', '0817347'),
   ('59614-58753', 'CRS02', '0889300'),
   ('25856-40101', 'CRS03', '0838756'),
   ('98208-93540', 'CRS04', '0832660'),
   ('64756-68937', 'CRS05', '0820356'),
   ('61092-02471', 'CRS06', '0874162'),
   ('94203-26749', 'CRS07', '0855759'),
   ('42374-12317', 'CRS08', '0861973'),
   ('83265-24378', 'CRS09', '0891964'),
   ('78910-58311', 'PRJ01', '0827171'),
   ('52269-69987', 'PRJ02', '0886733'),
   ('59614-58753', 'PRJ03', '0885275'),
   ('25856-40101', 'PRJ04', '0837712'),
   ('98208-93540', 'PRJ05', '0803756'),
   ('64756-68937', 'PRJ06', '0832106'),
   ('52269-69987', 'CRS01', '0871899'),
   ('59614-58753', 'CRS02', '0834667'),
   ('25856-40101', 'CRS03', '0848042'),
   ('98208-93540', 'CRS04', '0882325'),
   ('64756-68937', 'CRS05', '0891031'),
   ('61092-02471', 'CRS06', '0846211'),
   ('94203-26749', 'CRS07', '0853161'),
   ('42374-12317', 'CRS08', '0819175'),
   ('83265-24378', 'CRS09', '0839441'),
   ('78910-58311', 'PRJ01', '0857631'),
   ('52269-69987', 'PRJ02', '0857382'),
   ('59614-58753', 'PRJ03', '0816380'),
   ('25856-40101', 'PRJ04', '0808692'),
   ('98208-93540', 'PRJ05', '0875869'),
   ('64756-68937', 'PRJ06', '0800811'),
   ('52269-69987', 'CRS01', '0838774'),
   ('59614-58753', 'CRS02', '0848415'),
   ('25856-40101', 'CRS03', '0840712'),
   ('98208-93540', 'CRS04', '0889517'),
   ('64756-68937', 'CRS05', '0804162'),
   ('61092-02471', 'CRS06', '0870247'),
   ('94203-26749', 'CRS07', '0823855'),
   ('42374-12317', 'CRS08', '0885385'),
   ('83265-24378', 'CRS09', '0852696'),
   ('78910-58311', 'PRJ01', '0882301'),
   ('52269-69987', 'PRJ02', '0835161'),
   ('59614-58753', 'PRJ03', '0822564'),
   ('25856-40101', 'PRJ04', '0821986'),
   ('98208-93540', 'PRJ05', '0812982'),
   ('64756-68937', 'PRJ06', '0843262'),
   ('52269-69987', 'CRS01', '0805577'),
   ('59614-58753', 'CRS02', '0845288'),
   ('25856-40101', 'CRS03', '0859163'),
   ('98208-93540', 'CRS04', '0810022'),
   ('64756-68937', 'CRS05', '0857370');

Insert Into workson Values
   ('64756-68937', 'CRS01', 'HMW37', 'reviewer'),
   ('98208-93540', 'CRS01', 'HMW54', 'solver'),
   ('52269-69987', 'CRS01', 'HMW41', 'reviewer'),
   ('52269-69987', 'CRS01', 'HMW44', 'designer'),
   ('59614-58753', 'CRS05', 'HMW39', 'designer'),
   ('42374-12317', 'CRS05', 'HMW03', 'solver'),
   ('98208-93540', 'CRS05', 'HMW66', 'solver'),
   ('83265-24378', 'CRS05', 'HMW99', 'solver'),
   ('98208-93540', 'PRJ01', 'HMW59', 'solver'),
   ('83265-24378', 'PRJ02', 'HMW59', 'reviewer'),
   ('83265-24378', 'PRJ01', 'HMW23', 'designer'),
   ('52269-69987', 'PRJ01', 'HMW04', 'reviewer'),
   ('94203-26749', 'PRJ04', 'HMW74', 'designer'),
   ('25856-40101', 'PRJ04', 'HMW13', 'reviewer'),
   ('64756-68937', 'PRJ04', 'HMW62', 'reviewer'),
   ('98208-93540', 'PRJ04', 'HMW78', 'solver'),
   ('42374-12317', 'PRJ04', 'HMW34', 'solver'),
   ('61092-02471', 'PRJ04', 'HMW55', 'solver'),
   ('98208-93540', 'PRJ04', 'HMW16', 'designer'),
   ('64756-68937', 'PRJ04', 'HMW77', 'reviewer');

Answer 1

我想你可以试试这个。

如果你想获得 NULL 数据，你可以使用 t.teacherbsn IS NULL 而不是 t.teacherbsn =NULL

SELECT teacher.first_name ,workson.teacherbsn
FROM teacher
LEFT JOIN teaches ON teacher.bsn = teaches.teacherbsn
LEFT JOIN workson ON teacher.bsn = workson.teacherbsn
WHERE workson.teacherbsn IS NULL
ORDER BY teacher.first_name

fiddle :http://sqlfiddle.com/#!15/d7d3d0/2