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?

StackOverflow is not the proper place for this question. We do not write your code for you. You need to do your own coding and if you aren't sure why something is not working as expected, post the code with an explanation of what you were expecting it to do, and what it is actually doing including all error messages. See [ask advice](http://stackoverflow.com/questions/ask-advice). — John Conde, Feb 03 '13 at 18:35
Most of the questions I have seen on SO are asking for code from others. If you're asking whether I have tried anything, yes I have. But it hasn't worked and to get an answer for that needs a specific question with examples just like I've given. If you know an answer but not willing to show it here, don't bother typing all that advice because I more-or-less know when to ask a question and when to search through `Google` for it. — mavili, Feb 03 '13 at 18:39
If you don't show any code, we have to assume you haven't made an effort. And most of the people here are asking for help with their code. Not for us to write their code. Big difference. — John Conde, Feb 03 '13 at 18:41

Taryn · Accepted Answer · 2013-02-03T18:45:06.940

4

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

edited Feb 03 '13 at 18:45

answered Feb 03 '13 at 18:39

Taryn

242,637
56
362
405

why join? any reason or constraints for this ? – Arpit Feb 03 '13 at 18:41
@Arpit They have a join table `Clinic_has_Doctor` that assigned the doctor to the clinic which is what they are asking for. – Taryn Feb 03 '13 at 18:42
oh! +1 for the link this is what i want to clear from last 3 months. – Arpit Feb 03 '13 at 18:56

score 2 · Answer 2 · answered Feb 03 '13 at 18:39

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

answered Feb 03 '13 at 18:39

BellevueBob

9,498
5
29
56

@JulienPalard Don't remember; I'm mostly self-taught. Putting commas at the beginning of lists makes it easier (for me) to make changes. I can insert or delete the entire line. Just a habit, same as my peculiar indentation style. – BellevueBob Feb 03 '13 at 18:47
thanks for your answer. it worked, but I had to mark @bluefeet's answer as the accepted one as it's simpler (even though only in typing side). – mavili Feb 03 '13 at 18:50

MySQL three-table join

2 Answers2