Getting data from unrelated tables

Question

I've got this homework for database 'SCHOOL'

Table Student (StudentID, Name, Surname, etc..)\
Table Application (ApplicationID, StudentID, ClassID)\
Table Class (ClassID, ClassName, TeacherID)\
Table Teacher (TeacherID, Name, Surname, etc..)

There are some other tables and columns in the database but I don't think they're important to this query.

I need to get name of the teachers for whose classes no student signed up. Let's say there is no one signed up for Math class, and I need to get the name of the teacher for that Math class.

SELECT Teacher.Name, Teacher.Surname, Class.ClassName
FROM Teacher
INNER JOIN Class ON Class.TeacherID = Teacher.TeacherID
INNER JOIN Application ON Application.ClassID = Class.ClassID
INNER JOIN Student ON Student.StudentID = Application.StudentID
WHERE Application.PredmetID IS NULL

Help would be appreciated.

Hint - use "where not exists (...)"

SMor
– SMor

2020-11-08 14:41:55 +00:00
Commented Nov 8, 2020 at 14:41 — SMor
– SMor, Commented Nov 8, 2020 at 14:41
Have a look at NOT EXISTS or a LEFT JOIN.

Thom A
– Thom A ♦

2020-11-08 14:42:27 +00:00
Commented Nov 8, 2020 at 14:42 — Thom A
– Thom A ♦, Commented Nov 8, 2020 at 14:42
I recommend using table aliases.

Dale K
– Dale K

2020-11-09 01:56:08 +00:00
Commented Nov 9, 2020 at 1:56 — Dale K
– Dale K, Commented Nov 9, 2020 at 1:56

Gordon Linoff · Accepted Answer · 2020-11-08 18:03:59Z

1

I would recommend NOT EXISTS:

select t.name
from class c join
     teacher t
     on t.teacherid = c.teacherid
where not exists (select 1
                  from application a
                  where a.classid = c.classid
                 );

answered Nov 8, 2020 at 18:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ismail Diari · Accepted Answer · 2020-11-08 14:49:10Z

1

I need to get name of the teachers for whose classes no student signed up

Select T.name
From Teacher T inner join [Class] C on T.TeacherId = C.TeacherId
Where C.ClassId not in (Select Distinct ClassId from Application)

answered Nov 8, 2020 at 14:49

Ismail Diari

5193 silver badges9 bronze badges

Collectives™ on Stack Overflow

Getting data from unrelated tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related