Doing multiple joins in a query MySQL

Question

I am fairly new to MySQL and I am having some trouble figuring this out.

I have two tables called doctors and doctor_tags

Fields for doctors: doctor_id, doctor_name

Fields for doctor_tags: id, doctor_id, tag_name

Now for a specific doctor_id I need to find related doctors. Doctors are related if they have the same tag_name. Once I find this relation I need to get doctor_name of all these doctors back.

I am almost completely lost I've got this far (I am sure it's wrong)

SELECT doctors.doctor_name, doctors.doctor_id FROM doctors INNER JOIN doctors_tags ON doctors.doctor_id = doctors_tags.doctor_id ...

I konw this query is pretty useless but I know we need an some type of join.

For whoever is kind enough to comeup with some sort of query, I would be very thankful if you could explain each part of it in the process. Thanks :)

EDIT: Part 2

If lets say we introduce another table that has an N:N relationship between doctor_id and tag_id

Table fields doctor_tags_joins: doctor_id,tag_id

and so the fields for the tag table change to

doctors_tags: tag_id, tag_name and so on...

how can we do the same thing with this additional table included into the mix.

Barmar · Accepted Answer · 2014-05-30 15:25:18Z

2

You need to join the tables twice:

SELECT DISTINCT d1.doctor_id, d2.doctor_name
FROM doctors AS d1
-- Get first doctor's tags
JOIN doctor_tags AS dt1 ON d1.doctor_id = dt1.doctor_id
-- Get all the other doctor_tags rows with the same tags
JOIN doctor_tags AS dt2 ON dt1.tag_name = dt2.tag_name 
    -- Don't list doctors as related to themselves
    AND dt1.doctor_id != dt2.doctor_id
-- Get those doctors' names
JOIN doctors AS d2 ON dt2.doctor_id = d2.doctor_id
ORDER BY d1.doctor_id, d2.doctor_name

I use DISTINCT so we don't get multiple rows for the same doctors if they have multiple tags in common.

If you're just looking up doctors related to a specific doctor, rather than all doctors, the query becomes:

SELECT doctor_name
FROM doctor_tags AS dt1
JOIN doctor_tags AS dt2 ON dt1.tag_name = dt2.tag_name AND dt1.doctor_id != dt2.doctor_id
JOIN doctors AS d2 ON dt2.doctor_id = d2.doctor_id
WHERE dt1.doctor_id = :doctor_id

where :doctor_id is a placeholder for the given doctor. You don't need the first doctor table because you're not using any information from that table.

If you want to get all the related doctor names on one line, use GROUP_CONCAT:

SELECT d1.doctor_id, GROUP_CONCAT(DISTINCT d2.doctor_name) AS related_doctors
FROM doctors AS d1
JOIN doctor_tags AS dt1 ON d1.doctor_id = dt1.doctor_id
JOIN doctor_tags AS dt2 ON dt1.tag_name = dt2.tag_name AND dt1.doctor_id != dt2.doctor_id
JOIN doctors AS d2 ON dt2.doctor_id = d2.doctor_id
GROUP BY d1.doctor_id

For your second question, the queries are the same, except you use doctor_tags_join instead of doctor_tags. Since you're not showing the tag names in the results, the doctor_tags table is irrelevant.

SELECT DISTINCT d1.doctor_id, d2.doctor_name
FROM doctors AS d1
JOIN doctor_tags_join AS dt1 ON d1.doctor_id = dt1.doctor_id
JOIN doctor_tags_join AS dt2 ON dt1.tag_id = dt2.tag_id 
    AND dt1.doctor_id != dt2.doctor_id
JOIN doctors AS d2 ON dt2.doctor_id = d2.doctor_id
ORDER BY d1.doctor_id, d2.doctor_name

edited May 30, 2014 at 15:25

answered May 29, 2014 at 17:58

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

9 Comments

Kodlee Yin Over a year ago

Would a subquery solution be slower than a triple join? eg.

SELECT doctors.doctor_name FROM doctors INNER JOIN doctor_tags ON doctor_tags.doctor_id = doctors.doctor_id WHERE doctor_tags.tag_name IN ( SELECT tag_name FROM doctor_tags WHERE doctor_id = '%' )

Barmar Over a year ago

They might be treated the same, check what EXPLAIN says.

Kodlee Yin Over a year ago

Ok. I just ask this because it seems a little more... intuitive with the subquery.

Barmar Over a year ago

MySQL doesn't always optimize IN (SELECT...) very well, so I try to avoid it.

Joe Meyer Over a year ago

JOIN is implicitly the same as an INNER JOIN

|

neiha · Accepted Answer · 2014-05-29 19:08:46Z

You can archieve this by using sub-queries. A subquery is just a query inside another query. Here's how it can be constructed step by step

First you want the tag_name that your specified doctor_id has, right? That is:

select tag_name from doctors_tags where doctor_id='(your doctor_id)'

To get all the doctors with the same tag_name

SELECT doctor_id 
FROM doctors_tags 
WHERE tag_name in(
 SELECT tag_name FROM doctors_tags 
 WHERE doctor_id='(your doctor_id)' 
)

and the final query, obtaining the names would be:

select doctors.doctor_name, doctors.doctor_id 
from doctors, doctors_tags 
where doctors_tags.tag_name in(
  SELECT tag_name FROM doctors_tags 
  WHERE doctor_id='(your doctor_id)' 
)
and doctors.doctor_id = doctors_tags.doctor_id;

hope it helps

edit

after including your doctor_tags_joins table, the thing looks like this

select doctors.doctor_name, doctors.doctor_id
from doctors, doctors_tags, doctors_tags_joins
where doctors_tags.tag_name in
(
    select tag_name 
    from doctors_tags, doctors_tags_joins
    where doctors_tags_joins.doctor_id=(your id)
    and doctors_tags.tag_id=doctors_tags_joins.tag_id
)
and doctor_tags_joins.tag_id=doctors_tags.tag_id
and doctors.doctor_id=doctors_tags.doctor_id;

Joe Meyer · Accepted Answer · 2014-05-29 20:45:46Z

1

SELECT DISTINCT r.doctor_id, r.doctor_name
FROM doctors d
    INNER JOIN doctor_tags dt_d
        ON d.doctor_id = dt_d.doctor_id
    INNER JOIN doctor_tags dt_r
        ON dt_d.tag_name = dt_r.tag_name
    INNER JOIN doctors r
        ON dt_r.doctor_id = r.doctor_id
WHERE d.doctor_id = 12345

edited May 29, 2014 at 20:45

answered May 29, 2014 at 17:58

Joe Meyer

4,4452 gold badges23 silver badges28 bronze badges

Collectives™ on Stack Overflow

Doing multiple joins in a query MySQL

3 Answers 3

9 Comments

edit

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

edit

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related