Consider the personnel table that stores information about you and your coworkers.
mysql> DESCRIBE personnel;
The id column holds the unique identifier of each employee and the boss column references the boss of each employee.
Which query employees’ names along with the name of their bosses?
A.
. SELECT * FROM personnel, personnel WHERE personnel.id=personnel.boss
B.
SELECT pi.name, p2.name FROM personnel AS pi SELF JOIN personnel AS p2 ON pl.id=p2.boss
C.
SELECT name, name FROM personnel LEFT JOIN personnel ON id=boss
D.
SELECT pi.name, p2.name FROM personnel AS pi LEFT JOIN personnel AS p2 ON pl.id=p2.boss
E.
You cannot produce this list with only one query.
Explanation:
Names of tables and columns can’t be ambiguous (a,c) and SELF JOIN (b) is bogus.
— better query (finds employees first, and then matches them with their bosses, until the last person has no boss).SELECT p1.name AS employee, p2.name AS boss
FROM personnel As p1
LEFT JOIN personnel AS p2
ON p2.id = p1.boss;
+———-+——-+
| employee | boss |
+———-+——-+
| Allen | Peter |
| Pat | Peter |
| Mary | Kat |
| Ann | Kat |
| Peter | Kat |
| Kat | NULL |
+———-+——-+