How to select from database with relations?

Question

Hi, I have database with schema on picture below and I need to select everything related to one row (one id) of [letaky]. That means the related [zamestnanci], every related [obsah] and every [knihy] in it.

This is the first time i used relations in database and i have no idea how to make such a select.

Answer 1

Use JOIN ... ON:

SELECT * 
FROM zamestnanci
JOIN lekaty ON lekaty.zamestnanciid = zamestnanci.id
JOIN obsah ON obsah.idletaku = lekaty.id
JOIN knihy ON knihy.id = obsah.idknihy
WHERE letaky.id = 123

You may also want to consider whether you need INNER JOIN, LEFT JOIN or RIGHT JOIN for each of these joins. The difference between these JOINs is described in many other questions on StackOverflow, for example this one:

http://stackoverflow.com/questions/419375/sql-join-differences