MySQL, how to display fields from different tables using scalar subqueries?

Question

MySQL

How can I display the number of flights and the person's name if they are in different tables?
1 task for self-
checking You can't merge tables

Here is the task itself


This is the database schema

Answer 1

What if the passenger checked in but did not buy tickets?
And if several passengers have the same name?
This is how you can do it with table joins:

SELECT COUNT(pit.id) AS count, MAX(p.name) AS name
FROM Passenger AS p
LEFT JOIN Pass_in_trip AS pit ON p.id = pit.passenger
GROUP BY p.id

And like this without joining, in a nested query:

SELECT (SELECT COUNT(pit.id) FROM Pass_in_trip AS pit WHERE p.id = pit.passenger) AS count, p.name
FROM Passenger AS p

Answer 2

The topic "Nested SQL queries" does not say anything?

Answer 3

what is the meaning of copy-paste in this case? turn on the brain
error in unrecorded namesakes.