An Example of Multiple MySQL Join Clauses
If you’ve landed here, I’m assuming you were looking for some MySQL join help. I’ve got just the thing. Let’s start with a scary query and get that out of the way first. Make sure you read to the end though. It’s no good if you run off screaming after just reading the MySQL code:
SELECT volunteers.volunteerFirstName, volunteers.volunteerLastName, volunteers.volunteerEmail, volunteers.volunteerPhone, volunteers.volunteerAddress, positions.positionTitle, notes.noteContent FROM volunteers LEFT JOIN volunteerPositions ON volunteers.volunteerID = volunteerPositions.volunteerID LEFT JOIN positions ON volunteerPositions.positionID = positions.positionID LEFT JOIN notes ON volunteers.volunteerID = notes.volunteerID WHERE volunteers.volunteerID = 1;
You still with me? Great. That’s as bad as it’s going to get, I promise.
Why This Query?
I’ll give a little backstory since this query is going to be used out in the wild…
As a former foster parent, I’ve got a soft spot for others who have opened their homes to kids. Mine was more of a steady placement (my wife and I committed to keeping the 10-year-old boy until he was 18, since he was considered “unadoptable” — not true, by the way, since we adopted him when he was about 16), but a lot are children who have just been removed from their homes with nothing but a trash bag’s worth of belongings.
There’s an organization near me, BelovedChildren, that among other things provides clothes and toys to those children at no cost to the foster parents.
This is where the query and multiple MySQL join clauses come in. I’ve volunteered to write a PHP application for them so that they can keep track of volunteers. They want a list of contacts, essentially. But they need to know in a hurry who can fill a role when they need it filled. I’ll show you how I laid it out.
mysql> show tables; +----------------------+ | Tables_in_volunteers | +----------------------+ | notes | | positions | | users | | volunteerPositions | | volunteers | +----------------------+
In simple queries, we can do things like
SELECT volunteerFirstName from volunteers, but if you look back at the big query, you see that I prefaced all of my column names with table names. When we’re dealing with a MySQL join, we must do this — otherwise, there’s no way to tell which table we’re wanting data from. And if there’s a
volunteerID column in more than one table (there is in this case), then we have to specify which one, and we do that with the table name prefix.
The Table Data Used in These MySQL Join Clauses
I’m hoping this image will help show what I did. Let’s take a look at the scary query again — this time with an explanation. If you’re anything like me, it might be useful to print that image off and have it handy when reading through the text here…
The Query and MySQL Join Clauses Explained
Select queries, whether they’ve got multiple left join clauses in them or not, have different parts. You ask for data (columns, rows), specify where you want it coming from (tables), and add any other criteria you want the data to match (certain ID numbers, date ranges, etc.). Let’s break this one down:
SELECT volunteers.volunteerFirstName, volunteers.volunteerLastName, volunteers.volunteerEmail, volunteers.volunteerPhone, volunteers.volunteerAddress, positions.positionTitle, notes.noteContent
These are the columns we want to show in our results. Notice again that each column is prefaced by its table name. There’s not too much to explain here.
FROM volunteers LEFT JOIN volunteerPositions ON volunteers.volunteerID = volunteerPositions.volunteerID LEFT JOIN positions ON volunteerPositions.positionID = positions.positionID LEFT JOIN notes ON volunteers.volunteerID = notes.volunteerID
Ahh, here it is, where the rubber meets the road — MySQL join clauses galore. I assure you, this query is nothing compared to a lot I’ve seen. But if you can grasp this concept, you’re well on your way to bigger things.
Refer to the image while we’re going here. We want things from the
volunteer table. That’s easy. But we also want related things from the
positions table. This is a problem because there’s nothing in the
positions table that is at all related to the
volunteers table. But there’s this other
volunteerPositions table. It has a
volunteerID we can associate with the
volunteerID in the
volunteers table. And it’s got a
positionID that correlates to the same column in the
positions table. If we want related data from the
positions table, we’ve got to involve the
volunteerPositions table, and we do it with the MySQL join clauses.
Hopefully the colored dots in the image help with this. I’m asking, Show me the Position title that corresponds with a volunteer. To gather that, I’m saying, Use the volunteerID in the volunteerPositions table as a common piece of information, then use positionId as a go-between for volunteerPositions and positions.
After those mouthfuls, I throw in another join that grabs any notes about the volunteer, again using
volunteerID as a common flag.
WHERE volunteers.volunteerID = 1;
This one’s easy after the last section. I just want to see information for anyone with a
volunteerID of 1. Our WHERE clause can have other requests, like
WHERE volunteers.volunteerZip = 04002 or
WHERE positions.positionTitle LIKE '%pipefitter%'.
Did this make sense? If we dove in too deep, that’s okay, we can help. Linux Academy will be launching a Database Administration and SQL Language Basics course, taught by our very own Matt Pearson. It covers installing a fresh instance of MySQL, creating a database and tables, inserting and updating data, editing users and permissions, and a lot more. It’s just what someone wanting to dive into databases needs when they’re getting started. I’m self-taught, and I can assure you, taking this course will be a much faster way to learn.