How to join a table many times in a single SQL query statement?

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

More than one join can create a messy SQL statement.

If you don’t use aliases they can be difficult to read.

If your database tables need to have many columns with the same relationship to another table. You can join them with aliases in a single efficient query.

Need a working example, with sample SQL statements? Check out my other post: How to join the same table twice in a single SQL query statement?

For more about MySQL please check out the MySQL terminology guide

Interested in more SQL tips? Please subscribe in the box below, for a weekly update of my newest posts every Tuesday!

When to use USING in a JOIN rather than JOINing ON?

What is the difference between ON and USING() when JOINing two or more tables together in a SQL query?

For example you have the following two tables, Users and Products, when a Product is created, the Users id that created it is stored.

Products table:
id Primary Key
created_by_user_id Foreign Key to Users table
product_name
Users table:
id Primary Key
first_name
last_name

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

SELECT p.*, u.*
FROM products p
INNER JOIN users u ON p.created_by_user_id = u.id

If however the user id field was named the same in both tables like this:

Products table:
id Primary Key
user_id Foreign Key to Users table
product_name
Users table:
user_id Primary Key
first_name
last_name

You can then use USING to JOIN the tables together and return the same data with this SQL:

SELECT p.*, u.*
FROM products p
INNER JOIN users u USING(user_id)

Each time you JOIN a table you will need to use an additional USING clause even if the second and third tables contain the exactly same column name.

Make sure that when you are JOINing tables together using the ON or the USING clause that the data type of the matching columns is the same data type.

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

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

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

You might not have even thought it was possible to have multiple joins to the same table.

It is possible and easy to do with the help of aliases.

Aliases for table names

Aliases allow you to reference the table name with an abbreviation.

For example if you have a table called “products” then you reference that table by creating an alias of “p”.

With MySQL you easily create an alias like this:

SELECT p.id FROM products p

Multiple joins to the same table can quickly create a messy SQL statement. If you don’t use aliases they can become difficult to read.

Alias SQL example:

If your database tables need to have two 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.

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

In this example, we use the aliases p for the products table and u for the users table:

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 using the same aliases:

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

But how can you combine both queries to know the user that created and modified the products in one query?

Just join the Users table twice, but you need to use a different alias each time you reference the same table.

In this example, we use the aliases u1 for the first reference and u2 for the second reference:

SELECT
p.id
, u1.firstname AS 'creator_firstname'
, u1.lastname AS 'creator_lastname'
, u2.firstname AS 'modifier_firstname'
, u2.lastname AS 'modifier_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

But what if a product has only been created and hasn’t yet been modified? In which case the modified_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 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'
FROM products p
INNER JOIN users u1
ON p.created_by_user_id = u1.id
LEFT JOIN users u2
ON p.modified_by_user_id = u2.id

So now you can join the same table twice 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!

MySQL phpMyAdmin Tutorial, creating tables and joining tables together

Check out my first video tutorial on YouTube.

I’m demonstrating MySQL phpMyAdmin, to get you started creating a database, creating and populating two MySQL tables and a simple SELECT statement showing how to join the two tables together.

The text size does look a little small, so the next video will be zoomed in to make it easier to see, stay tuned for more.

If you have any questions you would like answered in a video, just leave them in comments below.

Thanks,

Jon

How to join three MySQL database tables together in a simple SELECT statement

Following on from my post of how to join two MySQL tables together, adding a third table is just as easy, but if you need an example this one extends the previous post by adding a third table.

For example I have a table of products, table of product categories and table of product suppliers, how to return the category description of each product and the supplier of the product?

Products
———–
id
category_id
supplier_id
product_name

Categories
————-
id
description

Suppliers
—————
id
supplier_name
supplier_address

In it’s simplest form you would join three tables based on the relationship between a key and it’s foreign key stored in another table:

SELECT p.id, p.product_name, c.description AS ‘cat_description’, s.supplier_name
FROM products p
INNER JOIN categories c on p.category_id = c.id
INNER JOIN suppliers s ON p.supplier_id = s.id

Using a well designed relational database and JOINed queries will make your PHP code simpler and more efficient by returning the data from two or more tables in one query.

Like this post? please leave a comment below: