CODEWITHSUNDEEP
mysqlsqljoin
Lucas Borges
Lucas Borges

Reputation: 142

Joining three tables with parent-child relationship

Suppose that we have four tables in the following structure (Table name - fields):

person - id, name
doctor - id_person, specialty
pacient - id_person, disease
appointment - doctor_id, pacient_id, date

How can I construct a query to return the doctor's name and specialty, the pacient's name and disease and the appointment date?

Here's where I've got so far:

SELECT person.name, doctor.specialty, pacient.disease, appointment.date
FROM appointment
INNER JOIN person
ON appointment.pacient_id=person.id
INNER JOIN doctor
ON appointment.doctor_id=doctor.id_person
INNER JOIN pacient
ON appointment.pacient_id=pacient.id_person

But this is not returning the right fields. I think the problem resides in returning the same field (person.name) for two different ids (doctor and pacient) in the same row.

Upvotes: 0

Views: 813

Answers (1)

SqlZim
SqlZim

Reputation: 38023

You need to do two separate joins to the person table, and use aliases to identify the individual tables like so:

select 
    dp.name as DoctorName
  , doctor.specialty
  , pp.name as PacientName
  , pacient.disease
  , appointment.date
from appointment

  inner join doctor
   on appointment.doctor_id = doctor.id_person
  inner join person dp
   on appointment.doctor_id = dp.id

  inner join pacient
   on appointment.pacient_id = pacient.id_person
  inner join person pp
   on appointment.pacient_id = pp.id

Upvotes: 1

Related Questions