Chain of Related Tables in Python

Now let's say that users live in certain cities, and these cities are located in different countries. In this case, we will need three tables for storage: users will be linked to cities, and cities - to countries. At the same time, we will not need a field for linking users to countries - because users will be linked to countries through the link between cities and countries.

Let's look at our tables. Table with countries:

countries
id	name
1	country1
2	country2

Table with cities:

cities
id	name	country_id
1	city1	1
2	city2	1
3	city3	2

The table with users will remain unchanged:

users
id	name	city_id
1	user1	1
2	user2	1
3	user3	2
4	user4	1
5	user5	3
6	user6	2

Requests

In order to get users together with their cities and countries, we will have to make two joins: the first will join cities to users, and the second will join countries to cities:

SELECT
	users.name,
	cities.name as city_name,
	countries.name as country_name
FROM
	users
LEFT JOIN cities ON cities.id=users.city_id
LEFT JOIN countries ON countries.id=cities.country_id

Practical tasks

Let the goods belong to a certain subcategory, and the subcategories belong to a certain category. Describe the storage structure.

Write a query that will retrieve products along with their subcategories and categories.

Write a query that will retrieve subcategories along with their categories.