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.

The Tables

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

MySQL Table Data

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

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

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

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%'.

Now What?

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get actionable training and tech advice

We'll email you our latest articles up to once per week.