Connecting 4 tables

Question

I am only a beginner in SQL, and I have problem that I can not solve.

The problem is the following:

i have four tables

Student: matrnr, name, semester, start_date
Listening: matrnr<Student>, vorlnr<Subject>
Subject: vorlnr, title, sws, teacher<Professor>
Professor: persnr, name, rank, room

I need to list all the students that are listening the Subject of some Professor with samo name.

EDIT:

select s.* 
from Student s, Listening h
where s.matrnr=h.matrnr
and h.vorlnr in (select v.vorlnr from Subject v, Professor p                        
where v.gelesenvon=p.persnr and p.name='Kant');

This is how i solved it but i am not sure is it optimal solution.

Answer 1

Your approach is good. Only, you want to show students, but join students with listings thus getting student-listing combinations.

Moreover you use a join syntax that is out-dated. It was replaced more than twenty years ago with explicit joins (INNER JOIN, CROSS JOIN, etc.)

You can do it with subqueries only:

select * 
from Students, 
where matrnr in 
(
  select matrnr
  from Listening
  where vorlnr in
  (
    select vorlnr 
    from Subject
    where gelesenvon in
    (
      select persnr
      from Professor
      where name='Kant'
    )
  )
);

Or join the other tables:

select * 
from Students 
where matrnr in 
(
  select l.matrnr
  from Listening l
  inner join Subject s on s.vorlnr = l.vorlnr
  inner join Professor p on p.persnr = s.gelesenvon and p.name='Kant'
);

Or with EXISTS:

select * 
from Students s 
where exists
(
  select *
  from Listening l
  inner join Subject su on su.vorlnr = l.vorlnr
  inner join Professor p on p.persnr = su.gelesenvon and p.name='Kant'
  where l.matrnr = s.matrnr
);

Some people like to join everthing and then clean up in the end using DISTINCT. This is easy to write, especially as you don't have to think your query through at first. But for the same reason it can get complicated when more tables and more logic are involved (like aggregations) and it can become quite hard to read, too.

select distinct s.* 
from Students s 
inner join Listening l on l.matrnr = s.matrnr
inner join Subject su on su.vorlnr = l.vorlnr
inner join Professor p on p.persnr = su.gelesenvon and p.name='Kant';

At last it is a matter of taste.