How to scatter (group) data of different tables by sql columns?

Question

MySQL

Hello. There are two tables for example:
cities:
(id-name)
1 Moscow
2 Novosibirsk
and population figures by years
(id-city-year-value)
1 1 2019 10000
2 1 2020 11000
3 1 2021 22000
4 2 2019 111111
5 2 2020 2135
6 2 2021 13135 How

can these two tables be combined into the following one:
City name - Values ​​​​of 2019 - Value of 2020 - Value of 2021
Moscow 10000 11000 22000
Novosibirsk

?

Answer 1

If you need a universal code so that columns are added automatically every year, then one SQL query is not enough, some part of the query will have to be generated dynamically, for example, through PHP.
Well, I would use PIVOT.

Answer 2

Multiple JOINs. Specify the year in the ON condition.

Answer 3

SELECT mo.name, v1.val, v2.val, v3.val
FROM mo
JOIN val as v1 on v1.mo = mo.id and v1.year = 2019
JOIN val as v2 on v2.mo = mo.id and v2 .year = 2020
JOIN val as v3 on v3.mo = mo.id and v3.year = 2021
Something like that, right? Isn't it going to be very heavy when working with, let's say, 100 cities and for each 7 years