MySQL Inner Join Logic

DennisHacker
DennisHacker used Ask the Experts™
on
Hi.  I have three tables in a MySQL Database.  Let's call them 'doctor', 'meeting_assign', and 'meeting'.  The 'meeting' table has an ID field and information for where the meeting will be held.  The 'doctor' table has information about the doctor.  The 'meeting_assign' table links the doctor to a specific meeting.

I need to formulate a query that gives me a list of the doctors that attend a specific meeting, and I need help with the logic.

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Developer
Top Expert 2010
Commented:
Here is how to get doctors that have meetings assigned (doctor will show multiple times if more than one meeting exists):

SELECT *
FROM doctor d
INNER JOIN meeting_assign a ON a.Doctor_id = d.Id
INNER JOIN meeting m ON m.Id = a.Meeting_Id;

Open in new window


Same as above, except also show doctors with no meetings assigned.

SELECT *
FROM doctor d
LEFT OUTER JOIN meeting_assign a
    INNER JOIN meeting m ON m.Id = a.Meeting_Id
ON a.Doctor_id = d.Id;

Open in new window


Here is a SQL Fiddle demo.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial