SQL and MySQL course Β· Module 7: Joining tables (JOIN)

Joining three shelves at once

2 min read Β·
In this lesson3

The bridge does not have to span only two wings. JOIN clauses can be chained, adding shelf after shelf - one JOIN after another.

Two bridges: book, author and category

A book has two cross-references: autor_id (to autorzy) and kategoria_id (to kategorie). We can cross both bridges in one query:

SELECT k.tytul, a.nazwisko, kat.nazwa
FROM ksiazki k
INNER JOIN autorzy a ON k.autor_id = a.id
INNER JOIN kategorie kat ON k.kategoria_id = kat.id;

Each further INNER JOIN throws another bridge and adds columns from a new shelf. Aliases (k, a, kat) let us point unambiguously to the table a column comes from.

A chain of joins around loans

Most often we join three shelves around the wypozyczenia table. The wypozyczenia table itself is just a set of numbers: who (czytelnik_id), what (ksiazka_id), when (data_wypozyczenia). To turn it into a readable report, we add the surnames and titles:

SELECT w.data_wypozyczenia, c.nazwisko, k.tytul
FROM wypozyczenia w
INNER JOIN czytelnicy c ON w.czytelnik_id = c.id
INNER JOIN ksiazki k ON w.ksiazka_id = k.id;

Which table to start from?

It is worth starting (FROM) from the table that holds cross-references to the others - here wypozyczenia. Bridges lead from it to czytelnicy and ksiazki, so the further joins fall into place naturally. Bridge after bridge - and the numbers turn into a story!

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. How do we join three tables in one query?

  2. 2. To show the loan date, the reader and the book title, which table is it most convenient to start from (FROM) and attach the rest to?

Hands-on tasks in the game

  • Horizontal ordering

    Arrange the correct clause that attaches the kategorie table to the report on one line (left to right):

  • Code editor

    Write a query that shows the book's tytul, the author's nazwisko and the category name (nazwa). Join ksiazki (alias k) with autorzy (alias a) on k.autor_id = a.id and with kategorie (alias kat) on k.kategoria_id = kat.id, using INNER JOIN.

  • Vertical ordering

    Arrange the parts of a query joining three tables (wypozyczenia, czytelnicy, ksiazki) in the correct order (top to bottom):

  • Code editor

    Write a query that shows data_wypozyczenia, the reader's nazwisko and the tytul of the borrowed book. Join wypozyczenia w with czytelnicy c (on w.czytelnik_id = c.id) and with ksiazki k (on w.ksiazka_id = k.id) using INNER JOIN.

  • Click in order

    Click the elements in the order in which we build a query joining ksiazki with kategorie:

  • Code editor

    Write a query that shows tytul, the author's nazwisko, the category name (nazwa) and the book's rok_wydania. Join ksiazki (k) with autorzy (a) on k.autor_id = a.id and with kategorie (kat) on k.kategoria_id = kat.id, using INNER JOIN.

  1. QuestionJoining many tables
  2. QuestionWhich table to start a loan report from
  3. Put in orderAttaching the kategorie table
  4. Coding taskBook, author and category
  5. Put in orderA loan report from three tables
  6. Coding taskLoans: reader and title
  7. Click in orderJoining ksiazki with kategorie step by step
  8. Coding taskFull book report

Useful articles