SQL — Joining Multiple Tables and Shortcuts — Music Store Project

As we join more tables, it is important to keep track of schemas and use abbreviations to make SQL queries concise, yet still readable.

SELECT il.track_id, t.name track_name, m.name track_type, il.unit_price, il.quantity 
FROM invoice_line il
INNER JOIN track t ON il.track_id = t.track_id
INNER JOIN media_type m ON t.media_type_id = m.media_type_id
WHERE il.invoice_id = 4;

Here we have joined three tables via two join statements. We abbreviate each of the tables and in each of our selections and other STATEMENTS, we clarify which table we are referring to.

It’s very easy to make mistakes here, so need to be careful in specifying exactly which table and what piece of information we are trying to query. Further, we can change column names to provide additional clarity.

The scheme becomes our best friend and we bounce around the database and its tables. Through the scheme, we can see the key connections we need to join the tables.