How to join the same table three times in a single SQL query statement?

Having more than one join to the same table can be confusing.

More than one join can quickly create a messy SQL statement. If you don’t use aliases they can quickly become difficult to read.

If your database tables need to have three or more columns with the same relationship to another table. You can easily join them with the help of aliases, as explained below.

For example say you have the following two tables: Users and Products. When a Product is created, the Users id that created it is stored. Then when it’s modified the Users id that modified the record is stored. Finally a Users id is recorded to approve the changes.

Products table fields: Comments
id Primary Key
created_by_user_id Foreign Key to Users table
modified_by_user_id Foreign Key to Users table
approved_by_user_id Foreign Key to Users table
Users table fields: Comments
id Primary Key
first_name
last_name

If you want to find out who created the product, you can do that with a simple SQL query like this:

SELECT
p.id
, u.firstname AS 'creator_firstname'
, u.lastname AS 'creator_lastname'
FROM products p
INNER JOIN users u
ON p.created_by_user_id = u.id

If you want to find out who modified the product you can do that with a similar query:

SELECT
p.id
, u.firstname AS 'modifier_firstname'
, u.lastname AS 'modifier_lastname'
FROM products p
INNER JOIN users u
ON p.modified_by_user_id = u.id

If you want to find out who approved the product you can do that with a similar query:

SELECT
p.id
, u.firstname AS 'approver_firstname'
, u.lastname AS 'approver_lastname'
FROM products p
INNER JOIN users u
ON p.approved_by_user_id = u.id

But how can you combine all three queries to know the usera that created, modified and approved the products in one query?

Just join the Users table three times, but you need to use a different alias each time you JOIN the same table:

SELECT
p.id
, u1.firstname AS 'creator_firstname'
, u1.lastname AS 'creator_lastname'
, u2.firstname AS 'modifier_firstname'
, u2.lastname AS 'modifier_lastname'
, u3.firstname AS 'approver_firstname'
, u3.lastname AS 'approver_lastname'
FROM products p
INNER JOIN users u1
ON p.created_by_user_id = u1.id
INNER JOIN users u2
ON p.modified_by_user_id = u2.id
INNER JOIN users u3
ON p.approved_by_user_id = u3.id

But what if a product has only been created and modified but hasn’t yet been approved? In which case the approved_by_user_id should be null in your products table.  Then the above query won’t be able to join twice to the users table. Unless it has a user record with a null id, which it shouldn’t.  In this case you need to use a LEFT JOIN like this:

SELECT
p.id
, u1.firstname AS 'creator_firstname'
, u1.lastname AS 'creator_lastname'
, u2.firstname AS 'modifier_firstname'
, u2.lastname AS 'modifier_lastname'
, u3.firstname AS 'approver_firstname'
, u3.lastname AS 'approver_lastname'
FROM products p
INNER JOIN users u1
ON p.created_by_user_id = u1.id
INNER JOIN users u2
ON p.modified_by_user_id = u2.id
LEFT JOIN users u3
ON p.approved_by_user_id = u3.id

So now you can join the same table three times with aliases in single efficient query.

Here are all the SQL statements from above and the CREATE table statements:


For more about MySQL please check out the MySQL terminology guide

Interested in more SQL tips? Please pop your email in the box below and you’ll be sure to receive them!

Leave a Reply