MySQL / SQL – Join tables, use aliases, and filter the result

Using the same Chinook database as I used in the previous posts I am going to join two tables to create a query. The tables I am going to use this time are the Track table and the Genre table. As usual, I am going to display them first.

To start everything we have to go through the same process:

service mysql start
mysql -u root -p

The above commands will take you to the MySQL prompt where you can use the Chinook database.

show databases;
use Chinook;

Let’s take a look at the Track table:

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)

Now, 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.01 sec)

Let’s join these two tables to show the name of the tracks and the genres. To make it a little bit more challenging, I am going to filter the result to show only jazz tracks.

So, both tables have GenreId. Let’s use that to join the tables.

mysql> SELECT Track.Name AS Track, Genre.Name AS Genre
FROM Track
JOIN Genre
ON Track.GenreId = Genre.GenreId
LIMIT 10;
+-----------------------------------------+-------+
| Track                                   | Genre |
+-----------------------------------------+-------+
| For Those About To Rock (We Salute You) | Rock  |
| Balls to the Wall                       | Rock  |
| Fast As a Shark                         | Rock  |
| Restless and Wild                       | Rock  |
| Princess of the Dawn                    | Rock  |
| Put The Finger On You                   | Rock  |
| Let's Get It Up                         | Rock  |
| Inject The Venom                        | Rock  |
| Snowballed                              | Rock  |
| Evil Walks                              | Rock  |
+-----------------------------------------+-------+
10 rows in set (0.00 sec)

Whitout the LIMIT 10 I would have 3505 rows. For the purpose of this example I have limited the result to a number of 10.

What I want next is to filter the result to show me only the tracks that are “Jazz”. For that I am going to use the WHERE clause and I am not going to use the LIMIT.

mysql> SELECT Track.Name AS Track, Genre.Name AS Genre
FROM Track
JOIN Genre
ON Track.GenreId = Genre.GenreId
WHERE Genre.name = 'Jazz';
+--------------------------------------------------------------+-------+
| Track                                                        | Genre |
+--------------------------------------------------------------+-------+
| Desafinado                                                   | Jazz  |
| Garota De Ipanema                                            | Jazz  |
| Samba De Uma Nota Só (One Note Samba)                        | Jazz  |
| Por Causa De Você                                            | Jazz  |

.....

| End Of Romanticism                                           | Jazz  |
| Amanda                                                       | Jazz  |
| Despertar                                                    | Jazz  |
| OAM's Blues                                                  | Jazz  |
+--------------------------------------------------------------+-------+
130 rows in set (0.00 sec)

As expected, I got 130 tracks that are classified as “Jazz”

Leave a Reply