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:

Leave a Reply