Joining three shelves at once

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:

1SELECT k.tytul, a.nazwisko, kat.nazwa
2FROM ksiazki k
3INNER JOIN autorzy a ON k.autor_id = a.id
4INNER 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:

1SELECT w.data_wypozyczenia, c.nazwisko, k.tytul
2FROM wypozyczenia w
3INNER JOIN czytelnicy c ON w.czytelnik_id = c.id
4INNER 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!