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. — John Conde
– John Conde, Commented Feb 3, 2013 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
– mavili, Commented Feb 3, 2013 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
– John Conde, Commented Feb 3, 2013 at 18:41

Taryn · Accepted Answer · 2013-02-03 18:45:06Z

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 3, 2013 at 18:45

answered Feb 3, 2013 at 18:39

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Arpit Over a year ago

why join? any reason or constraints for this ?

Taryn Over a year ago

@Arpit They have a join table Clinic_has_Doctor that assigned the doctor to the clinic which is what they are asking for.

Arpit Over a year ago

oh! +1 for the link this is what i want to clear from last 3 months.

BellevueBob · Accepted Answer · 2013-02-03 18:39:46Z

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 3, 2013 at 18:39

BellevueBob

9,6345 gold badges32 silver badges56 bronze badges

2 Comments

BellevueBob Over a year ago

@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.

mavili Over a year ago

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).

Collectives™ on Stack Overflow

MySQL three-table join

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related