In this post I show you how to use JOIN statements to gather columns from other tables in MySQL.
Sometimes your MySQL database / table structure can grow in your project. Utilizing multiple tables schemes are a way to keep things organized but sometimes you want to get a column from another table in your query to add to your results. This is where JOIN comes in.
First I will show you a simple but “complex” MySQL scheme with multiple tables. I will break down the use for each table and column then finalize with using the JOIN statement.
MySQL Table Structures
The project is a list sync system. There are 3 tables.
mysql> desc creations;
+--------------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------+------+-----+---------+----------------+
| creations_id | int | NO | PRI | NULL | auto_increment |
| owner_id | int | NO | | NULL | |
| sync_with | int | YES | | NULL | |
+--------------+------+------+-----+---------+----------------+
mysql> select * from creations;
+--------------+----------+-----------+
| creations_id | owner_id | sync_with |
+--------------+----------+-----------+
| 1 | 148 | 163 |
| 2 | 163 | NULL |
| 3 | 149 | NULL |
+--------------+----------+-----------+- Creations table is used when the user creates a list. It is used to track who the list should be synced with. The owner_id and the sync_with are the ids used in the users table when they register for an account. They are generated a creations_id that is used later for all the lists they create.
mysql> desc creations_list;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| list_id | int | NO | PRI | NULL | auto_increment |
| creations_id | int | NO | | NULL | |
| items | varchar(255) | NO | | NULL | |
| title | varchar(255) | NO | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
mysql> select * from creations_list;
+---------+--------------+----------------------------------------+----------------+
| list_id | creations_id | items | title |
+---------+--------------+----------------------------------------+----------------+
| 1 | 1 | pork, sausage, eggs, bread | Grocery |
| 3 | 1 | nail gun, drill, shingles, drywall mud | Hardware Store |
| 4 | 2 | dozen donuts | Krispy Creme |
| 5 | 2 | french fries | McDonalds |
| 6 | 3 | acme cream | Pharmacy |
+---------+--------------+----------------------------------------+----------------+2. Creations_list is where the creations_id is used to identify who the entry belongs to. The rest of the entries are obvious. The list_id is generated when the list is created.
mysql> desc sync_requests;
+--------------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| owner_id | int | NO | | NULL | |
| sync_with | int | NO | | NULL | |
| creations_id | int | NO | | NULL | |
+--------------+------+------+-----+---------+----------------+
mysql> select * from sync_requests;
+----+----------+-----------+--------------+
| id | owner_id | sync_with | creations_id |
+----+----------+-----------+--------------+
| 1 | 149 | 163 | 3 |
+----+----------+-----------+--------------+3. Sync_requests is a sync requesting system. When a user asks to have another person sync to their lists, an entry is created. The other user then gets notified there is a sync request waiting for approval. When they accept, then the sync_with field in the creations tables is then filled with their user id. After approval or denial, the sync request entry is then deleted. This table mostly stays empty except for pending actions.
JOIN STATEMENT
So lets say you have and user that wants to view all the lists they are currently synced with. This includes the ones they created and the ones they share as the “sync_with partner”.
You could execute a MySQL query like this
mysql> SELECT * FROM creations_list WHERE creations_id IN (SELECT creations_id FROM creations WHERE owner_id = 163 OR sync_with = 163);
+---------+--------------+----------------------------------------+----------------+
| list_id | creations_id | items | title |
+---------+--------------+----------------------------------------+----------------+
| 1 | 1 | pork, sausage, eggs, bread | Grocery |
| 3 | 1 | nail gun, drill, shingles, drywall mud | Hardware Store |
| 4 | 2 | dozen donuts | Krispy Creme |
| 5 | 2 | french fries | McDonalds |
+---------+--------------+----------------------------------------+----------------+This works but what if you want to get the username from the users table so in your UI you can present this list and who the owner of the lists is.
This is where the JOIN statement can come in. When you use JOIN, you want to specify with the ON syntax. The ON part of the statement will then ensure the two columns are in sync when it returns the results.
mysql> SELECT creations_list.*, creations.*, users.username FROM creations_list join creations on creations_list.creations_id = creations.creations_id join users on creations.owner_id = users.id where creations_list.creations_id IN (SELECT creations.creations_id FROM creations WHERE creations.owner_id = 163 OR creations.sync_with = 163);
+---------+--------------+----------------------------------------+----------------+--------------+----------+-----------+----------+
| list_id | creations_id | items | title | creations_id | owner_id | sync_with | username |
+---------+--------------+----------------------------------------+----------------+--------------+----------+-----------+----------+
| 1 | 1 | pork, sausage, eggs, bread | Grocery | 1 | 148 | 163 | Winston |
| 3 | 1 | nail gun, drill, shingles, drywall mud | Hardware Store | 1 | 148 | 163 | Winston |
| 4 | 2 | dozen donuts | Krispy Creme | 2 | 163 | NULL | Russell |
| 5 | 2 | french fries | McDonalds | 2 | 163 | NULL | Russell |
+---------+--------------+----------------------------------------+----------------+--------------+----------+-----------+----------+This statement above joins multiple tables and includes specified columns from those tables to include in results.
