CODEWITHSUNDEEP
sqlpostgresql
Mike Keehnen
Mike Keehnen

Reputation: 45

SQL Query That Requires 3 Tables For The Solution

I need some help with making a query. I'm stuck and I'm kind of losing my motivation to proceed my project.

I have 3 tables:

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));

I need to find the teachers that do teach courses but do NOT work on assignments (workson)

This is what I thought: 

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

In this way the join should filter all teacherbsn's on existens so all I have to do is SELECT WHERE table.column_name = NULL, but this isn't working...

The anwser should be something like this:

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

If you need more info I'll be happy to give it to you!

This is the data I'm working with:

  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');

Upvotes: 3

Views: 52

Answers (1)

D-Shih
D-Shih

Reputation: 46219

I think you can try this.

If you want to get NULL data you might do t.teacherbsn IS NULL instead of 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

Upvotes: 3

Related Questions