In the real world of MySQL, we usually join more than two tables. This happens very often, so let's learn how to do it.
First, let's define our third table. If you remember, our schema was designed to look like this:
phones table
id: primary key
nameId: names key
number: string
phoneType: string ["mobile", "home", "business", etc...]create table phones (
id int unsigned not null,
nameId int unsigned not null,
number char(16) not null,
phoneType varchar(24) not null
) engine=innodb default charset=latin1;
alter table phones add primary key (id);
alter table phones modify id int unsigned not null auto_increment;And we show that out table has been created:
mysql> show tables;
+---------------------+
| Tables_in_employees |
+---------------------+
| addresses |
| names |
| phones |
+---------------------+
3 rows in set (0.00 sec)
mysql> describe phones;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| nameId | int unsigned | NO | | NULL | |
| number | char(16) | NO | | NULL | |
| phoneType | varchar(24) | NO | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)Our definition here uses the standard nameId field to link back to a row in the names table.
Let's create some phone number entries.
insert into phones set nameId=1, number="555-555-5555", phoneType="home";
insert into phones set nameId=1, number="121-555-5555", phoneType="cell";
insert into phones set nameId=2, number="345-555-5555", phoneType="home";
insert into phones set nameId=3, number="456-555-5555", phoneType="home";
insert into phones set nameId=4, number="896-555-5555", phoneType="home";
insert into phones set nameId=4, number="981-555-5555", phoneType="cell";
insert into phones set nameId=4, number="162-555-5555", phoneType="fax";
insert into phones set nameId=5, number="323-555-5555", phoneType="home";
insert into phones set nameId=6, number="896-555-5555", phoneType="home";
insert into phones set nameId=6, number="121-555-5555", phoneType="cell";
insert into phones set nameId=7, number="414-555-5555", phoneType="home";
insert into phones set nameId=7, number="213-555-5555", phoneType="cell";mysql> select * from phones;
+----+--------+--------------+-----------+
| id | nameId | number | phoneType |
+----+--------+--------------+-----------+
| 1 | 1 | 555-555-5555 | home |
| 2 | 1 | 121-555-5555 | cell |
| 3 | 2 | 345-555-5555 | home |
| 4 | 3 | 456-555-5555 | home |
| 5 | 4 | 896-555-5555 | home |
| 6 | 4 | 981-555-5555 | cell |
| 7 | 4 | 162-555-5555 | fax |
| 8 | 5 | 323-555-5555 | home |
| 9 | 6 | 896-555-5555 | home |
| 10 | 6 | 121-555-5555 | cell |
| 11 | 7 | 414-555-5555 | home |
| 12 | 7 | 213-555-5555 | cell |
+----+--------+--------------+-----------+
12 rows in set (0.00 sec)Let's do a two table join so that we can see each employee phone number:
mysql> select lastName, firstName, number, phoneType from names n, phones p where n.id=p.nameId;
+-----------+-----------+--------------+-----------+
| lastName | firstName | number | phoneType |
+-----------+-----------+--------------+-----------+
| Smith | Jim | 555-555-5555 | home |
| Smith | Jim | 121-555-5555 | cell |
| Rocker | Bob | 345-555-5555 | home |
| Kella | Shella | 456-555-5555 | home |
| Toga | Sara | 896-555-5555 | home |
| Toga | Sara | 981-555-5555 | cell |
| Toga | Sara | 162-555-5555 | fax |
| Flauntana | Hannah | 323-555-5555 | home |
| Found | Lost | 896-555-5555 | home |
| Found | Lost | 121-555-5555 | cell |
| Donkey | Man | 414-555-5555 | home |
| Donkey | Man | 213-555-5555 | cell |
+-----------+-----------+--------------+-----------+
12 rows in set (0.01 sec)That was a pretty standard two table join, which you should be pretty familiar with at this point. Let's take a leap and jump in to joining all three tables, shall we?
Joining three tables using a standard join is pretty simple. It looks very similar. We just have to expand our query a little bit by adding a new table and one more where condition.
mysql> select lastName, firstName, state, number, phoneType from names n, addresses a, phones p where n.id=a.nameId and n.id=p.nameId;
+-----------+-----------+-------+--------------+-----------+
| lastName | firstName | state | number | phoneType |
+-----------+-----------+-------+--------------+-----------+
| Smith | Jim | CA | 555-555-5555 | home |
| Smith | Jim | CA | 121-555-5555 | cell |
| Rocker | Bob | CA | 345-555-5555 | home |
| Kella | Shella | OR | 456-555-5555 | home |
| Toga | Sara | WA | 896-555-5555 | home |
| Toga | Sara | WA | 981-555-5555 | cell |
| Toga | Sara | WA | 162-555-5555 | fax |
| Flauntana | Hannah | ID | 323-555-5555 | home |
| Found | Lost | CA | 896-555-5555 | home |
| Found | Lost | CA | 121-555-5555 | cell |
| Donkey | Man | IN | 414-555-5555 | home |
| Donkey | Man | IN | 213-555-5555 | cell |
+-----------+-----------+-------+--------------+-----------+
12 rows in set (0.00 sec)And, if we wanted to do it with a left join:
mysql> select lastName, firstName, state, number, phoneType from names n left join addresses a on n.id=a.nameId left join phones p on n.id=p.nameId;
+-----------+-----------+-------+--------------+-----------+
| lastName | firstName | state | number | phoneType |
+-----------+-----------+-------+--------------+-----------+
| Smith | Jim | CA | 555-555-5555 | home |
| Smith | Jim | CA | 121-555-5555 | cell |
| Rocker | Bob | CA | 345-555-5555 | home |
| Kella | Shella | OR | 456-555-5555 | home |
| Toga | Sara | WA | 896-555-5555 | home |
| Toga | Sara | WA | 981-555-5555 | cell |
| Toga | Sara | WA | 162-555-5555 | fax |
| Flauntana | Hannah | ID | 323-555-5555 | home |
| Found | Lost | CA | 896-555-5555 | home |
| Found | Lost | CA | 121-555-5555 | cell |
| Donkey | Man | IN | 414-555-5555 | home |
| Donkey | Man | IN | 213-555-5555 | cell |
+-----------+-----------+-------+--------------+-----------+
12 rows in set (0.00 sec)Let's look at the previous example for a minute, specifically the from statements area.
We'll zoom in on the from area specifically.
from names n left join addresses a on n.id=a.nameId left join phones p on n.id=p.nameId;In the first part of the join, we left join names and addresses. We're essentially joining the two together. But, in the next left join, we're only joining one table, phones. What's great about this situation is we only need to specify the next table to join on. MySQL will assume the rest. You don't need to specify that you want to left join names to phones. It deduces that from the n.id=p.nameId statement.
And one other thing you should notice is there are no commas in between each left join. The act of specifying left join is enough for MySQL.