The story of 3 little tables

[TapeGun007]

Here are the tables:

 

Table: Members

Member_ID

FirstName

LastName

 

Table: Departments

Dept_ID

Dept_Name

 

Table: Department_Members

DM_ID

Dept_ID

Member_ID

 

I want to be able to spit out the Dept_ID, Dept_Name, Member_ID, FirstName, LastName.

 

I just did a JOIN, but now this is kind of a 3 way join, is that possible?

[requinix]

It's not a three-way join. Just two regular joins.

Members  Department_Members  Departments
          ^                        ^
         JOIN                     JOIN

[TapeGun007]

SELECT Departments.Dept_ID, Departments.Dept_Name, Department_Members.Dept_ID, Department_Members.MemberID, Department_Members.DM_ID, Members.FirstName, Members.LastName
FROM Departments, Department_Members, Members
WHERE Departments.Dept_ID = Department_Members.Dept_ID
AND Members.MemberID = Department_Members.MemberID

 

I did this and it works, but... is this really the correct way?

 

I made a small mistake on the original example, MemberID isn't "Member_ID".

[requinix]

It's almost the "proper" way, but MySQL will likely optimize it in the right direction for you:

SELECT ...
FROM Departments
JOIN Department_Members ON Departments.Dept_ID = Department_Members.Dept_ID
JOIN Members ON Department_Members.MemberID = Members.MemberID

[ignace]

No need for the extra code:

 

SELECT ...
FROM Departments
JOIN Department_Members USING(Dept_ID)
JOIN Members USING(MemberID)

 

Table: Department_Members

DM_ID

Dept_ID

Member_ID

 

You don't need the DM_ID, Dept_ID & Member_ID already make a unique value.