Category Archives: SQL | MySQL

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.

Continue reading