Getting Data from Related Tables in Python

Let's make a request that will get all users along with their cities. For this we will need the command LEFT JOIN:

Its syntax is as follows:

SELECT fields FROM name_LEFT JOIN tables name_related_tables ON condition_connections WHERE condition_samples

Let's break down the individual parts of this command's syntax.

Fields

Since the selection comes from several tables, selecting all fields via * will not work. The following query will select fields only from the main table, but not from the related one:

SELECT *

To select data from all tables, you need to specify the table name for selection before *:

SELECT users.*, cities.*

Or we can list the fields we need with the table name in front of them:

SELECT users.name, cities.name

These two methods have a problem. The thing is that if the fields in the tables have the same names, then in the Python array there will be a name conflict and only one field will win, and the second will not be there.

To solve the problem, you need to rename conflicting names using the as command:

SELECT users.name, cities.name as city_name

Connection

After the ON command, we must specify the fields from the two tables that are used to establish the relationship. In our case, this will be the id field from the table with cities and the city_id field from the table with users:

ON cities.id=users.city_id

Request

As a result, the request that will get users together with their cities will look like this:

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

Practical tasks

Let's say you have a table with products and a table with their categories. Write a query that will get the names of the products along with their categories.