Getting two sets of information from one table in a select statement often leads people to write subselects, but it really doesn’t matter that this is the same table twice, we can just give it a new alias and treat it as if it were a different table. This is one of those techniques where, once you’ve seen it, it’s really obvious, but until that point it can be very confusing. I explained this to someone else recently, so I thought I’d capture it here in case it’s helpful to anyone else.
Consider that tried-and-tested example: employees and managers. Here’s the staff table from the database (today’s imaginary data isn’t particularly imaginative, sorry):
mysql> select * from staff; +----+------------+-----------+------------+ | id | first_name | last_name | manager_id | +----+------------+-----------+------------+ | 1 | Hattie | Hopkins | 4 | | 2 | Henry | Hopkins | 4 | | 3 | Harry | Hopkins | 5 | | 4 | Helen | Hopkins | NULL | | 5 | Heidi | Hopkins | 4 | | 6 | Hazel | Hopkins | 1 | +----+------------+-----------+------------+ 6 rows in set (0.00 sec)