MySQL / SQL Join 3 tables together and filter the result

I have looked at how to create queries using two tables. What if I need to join 3 tables to create a more complex query?

Let’s say for example that I need to create a query that shows the name of a track, the name of it’s MediaType, and the name of it’s genre. For that I will need to join 3 tables together with the appropriate join columns then I will have to add a filter to only show tracks with a MediaType of “Protected AAC audio file” and a Genre of “Soundtrack”.

As always, I need to get to the sql prompt. For that I need to run the following commands in the terminal:

service mysql start
mysql -u root -p
use Chinook;

Now, I need to take a look at the tables I need to use: Track, MediaType, and Genre.

mysql> show tables;
+-------------------+
| Tables_in_Chinook |
+-------------------+
| Album             |
| Artist            |
| Customer          |
| Employee          |
| Genre             |
| Invoice           |
| InvoiceLine       |
| MediaType         |
| Playlist          |
| PlaylistTrack     |
| Track             |
+-------------------+
11 rows in set (0.00 sec)

The above command lists all the tables from the Chinook database. To display the tables I want the desc command is used.

mysql> desc Track;
+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| TrackId      | int(11)       | NO   | PRI | NULL    | auto_increment |
| Name         | varchar(200)  | NO   |     | NULL    |                |
| AlbumId      | int(11)       | YES  | MUL | NULL    |                |
| MediaTypeId  | int(11)       | NO   | MUL | NULL    |                |
| GenreId      | int(11)       | YES  | MUL | NULL    |                |
| Composer     | varchar(220)  | YES  |     | NULL    |                |
| Milliseconds | int(11)       | NO   |     | NULL    |                |
| Bytes        | int(11)       | YES  |     | NULL    |                |
| UnitPrice    | decimal(10,2) | NO   |     | NULL    |                |
+--------------+---------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

The Track table has a column Name which I will use in the query!

mysql> desc MediaType;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| MediaTypeId | int(11)      | NO   | PRI | NULL    | auto_increment |
| Name        | varchar(120) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

The MediaType table has a column Name which I will use in the query as well! Finally, let’s take a look at the Genre table.

mysql> desc Genre;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| GenreId | int(11)      | NO   | PRI | NULL    | auto_increment |
| Name    | varchar(120) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

As expected, the Genre table has its own table named Name.

Let’s see how to join them together.

SELECT Track.Name as Track, MediaType.Name as MediaType, Genre.Name as Genre
FROM Track
	JOIN MediaType on Track.MediaTypeId = MediaType.MediaTypeId
	JOIN Genre on Track.GenreId = Genre.GenreId

Sure enough, I will get 3505 rows, since there is no filter in my query. Of course, They can’t all be shown here!


--------------------+-----------------------------+--------------------+
| Track                                                                                                                       | MediaType                   | Name               |
+-----------------------------------------------------------------------------------------------------------------------------+-----------------------------+--------------------+
| For Those About To Rock (We Salute You)                                                                                     | MPEG audio file             | Rock               |
| Put The Finger On You                                                                                                       | MPEG audio file             | Rock               |
| Let's Get It Up                                                                                                             | MPEG audio file             | Rock               |

...

| OAM's Blues                                                                                                                 | AAC audio file              | Jazz               |
| One Step Beyond                                                                                                             | AAC audio file              | Electronica/Dance  |
| Symphony No. 3 in E-flat major, Op. 55, "Eroica" - Scherzo: Allegro Vivace                                                  | AAC audio file              | Classical          |
+-----------------------------------------------------------------------------------------------------------------------------+-----------------------------+--------------------+
3503 rows in set (0.02 sec)

I don’t want all of these tracks, but only the ones that the MediType is “Protected AAC audio file” and the Genre is “Soundtrack”. For that I have to create a filter.

mysql> SELECT Track.Name as Track, MediaType.Name as MediaType, Genre.Name as Name
FROM Track
	JOIN MediaType ON Track.MediaTypeId = MediaType.MediaTypeId
	JOIN Genre ON Track.GenreId = Genre.GenreId
WHERE MediaType.Name = 'Protected AAC audio file' and Genre.Name = 'Soundtrack';
+---------------+--------------------------+------------+
| Track         | MediaType                | Name       |
+---------------+--------------------------+------------+
| Koyaanisqatsi | Protected AAC audio file | Soundtrack |
+---------------+--------------------------+------------+
1 row in set (0.00 sec)

The result is quite surprising! Only one track matches the query!

Leave a Reply