MySQL - More Join operators, more aliases and Error 1066

Question

Hopefully you are going to help me again :)

Well, the problem that I have is similar to the one which I've posted yesterday, but it is extended.

We are given three tables:

Pfleger

Station

Mitarbeiter

As states above, this problem is very similar to this problem.

What was the result? Well, I get back a table with the ID's and names of the workers who are living in Frankfurt.

Now I should additionally get back the ID'S and names of the workers who are living in Frankfurt AND working in the station called Onkologie.

How should I do this?

My code so far:

SELECT pfleger.PNR, Name
from mitarbeiter, ...
JOIN pfleger on (mitarbeiter.PNR=pfleger.PNR)
JOIN ...
where Ort='Frankfurt' and Name='Onkologie'

I don't know how to make 2nd JOIN.

Answer 1

You could try something like this

select m.PNR, m.Name
from Mitarbeiter m
inner join Station s on s.PNR = m.PNR
inner join Pfleger p on p.StationID = s.StationID
where
  m.Ort = 'Frankfurt'
  and p.Name = 'Onkologie'

Answer 2

Updated based on provided table names and column names for tables.

Notice: we eliminated the , notation after mitarbeiter, in your base query.

I don't see PNR in mitabeiter so I'm assuming stationID is how they join.

SELECT * 
FROM pfleger P
INNER JOIN Station S
 on S.StationId = P.StationID
INNER JOIN mitarbeiter M
 on M.pnr = S.pnr
WHERE M.ORT='Frankfurt' and P.name = 'Onkologie'

Assumptions I made: pfleger.stationID has a foreign key relationship to station.stationID mitarbeiter.PNR has a foreign key relationship to station.PNR

We used inner joins here as we only want Mitarbeiter who exist in all 3 tables. Otherwise we could use outer joins and return those who don't have records as well. (meaning it is unknown where they work it MIGHT be 'Onkologie' but we don't know as there is no record.)