Data Relationships in Python

Let us have the task of preserving fathers and sons. Let each father have only one son, and let the son in turn have only one son.

We need to figure out how we'll store the data. The first idea that might come to mind is to make two tables: parents for fathers and sons for sons. Then link these tables with some field: son_id or parent_id.

However, this is not a very good idea - after all, the same person can be both a father and a son at the same time - and you will have to store him in both tables, which is inconvenient, takes up more space and easily leads to errors.

A better option is to link the table to itself: make a table users, in it we will store all users and for each we will make a field son_id, in which id son from the same table will be stored:

Requests

Let's now write a query that will get the user along with his son.

First, let's just get the users:

sqlSELECT
  *
FROM
  users

Now let's join the users of their sons. We will join the table to itself, so we need to rename it:

sqlLEFT JOIN users as sons

Now we can specify the relationship between the base table and the renamed one:

sqlLEFT JOIN users as sons ON sons.id=users.son_id

Let's now specify the fields:

sqlSELECT
  users.name as user_name, sons.name as son_name

Let's put it all together and get the following query:

sqlSELECT
  users.name as user_name, sons.name as son_name
FROM
  users
LEFT JOIN users as sons ON sons.id=users.son_id

Practical tasks