MySQL three-table join

Question

I have three tables called Clinic, Doctor, and Clinic_has_Doctor. Suppose the two tables Clinic and Doctor have fields id and name as follows:

Clinic 
+----+------------------------+
| id | name                   |
+----+------------------------+

Doctor
+----+------------------------+
| id | name                   |
+----+------------------------+

And Clinic_has_Doctor is a simple table containing mappings between clinics and doctors:

Clinic_has_Doctor
+-----------+-----------------+
| Clinic_id | Doctor_id       |
+-----------+-----------------+

which expectedly are foreign keys to Clinic and Doctor IDs. I need to get the list of clinics and the doctors along them. So I would have a table like:

+-----------+--------------+------------+--------------+
| Clinic_id | Clinic_name  | Doctor_id  | Doctor_name  |
+-----------+--------------+------------+--------------+

I haven't done much JOIN SQL commands so I couldn't get out of this. Any help?

Answer 1

You will want to JOIN the tables using your Clinic_has_Doctor to connect the Clinic to the Doctor table:

select c.id ClinicId,
  c.name ClinicName,
  d.id DoctorId,
  d.name Doctor_name
from clinic c
inner join Clinic_has_Doctor cd
  on c.id = cd.clinic_id
inner join doctor d
  on cd.Doctor_id = d.id

If you need help learning JOIN syntax then here is a great visual explanation of joins

I used an INNER JOIN in the query which will return all Clinics and the doctors that are associated with them.

If you want to return a list of Clinics even if there is no doctor associated then you will use a LEFT JOIN:

select c.id ClinicId,
  c.name ClinicName,
  d.id DoctorId,
  d.name Doctor_name
from clinic c
left join Clinic_has_Doctor cd
  on c.id = cd.clinic_id
left join doctor d
  on cd.Doctor_id = d.id

Answer 2

Should be something like this:

select clinic_has_doctor.clinic_id
     , clinic.clinic_name
     , clinic_has_doctor.doctor_id
     , doctor.doctor_name
from   clinic_has_Doctor 
join   clinic 
on     clinic.id=clinic_has_Doctor.clinic_id
join   doctor
on     doctor.id=clinic_has_Doctor.doctor_id