In MySQL, how do I join two fields in one table to the same table's primary key? [closed]

Question

I am working with a MySQL backend (version 5.7.19), and a LibreOffice Base frontend(version 7.0.6.2 x64) on 64-bit Windows. I have a table that lists personnel with a primary key id. I also have a workorders table that has an "entered by" field and a "reviewed by" field, both of which need to store the id of the personnel who complete those tasks. If I wanted to have two foreign keys in one table pointing to the same table's primary key, what would my SELECT statement need to look like?

In my case, I have a table 'personnel' with two fields with ID as the primary key, thus:

ID	Name
1	John Smith
2	John Adams
3	Samuel Adams

Also, a table 'orders' with three fields with entered_by and reviewed_by as foreign keys to personnel.id

workorder	entered_by	reviewed_by
1	2	3
2	3	1

I know how to

SELECT WORKORDER, PERSONNEL.NAME AS ENTERED FROM ORDERS JOIN PERSONNEL ON PERSONNEL.ID = ORDERS.ENTERED_BY ORDER BY WORKORDER.ID;

and how to

SELECT WORKORDER, PERSONNEL.NAME AS REVIEWED FROM ORDERS JOIN PERSONNEL ON PERSONNEL.ID = ORDERS.REVIEWED_BY ORDER BY WORKORDER.ID;

but I'm not sure how to put them into a single query, so that I get:

workorder	entered	reviewed
1	John Adams	Samuel Adams
2	Samuel Adams	John Smith

Answer 1

Yes, according to relational algebra every pair of tables can have multiple relationships between them.

For example, the typical illustration of this case, is a money_transfer table that records money flowing from one account to another. In this case this table will have two foreign keys against the account table: one to indicate where the money is coming from, and the other to indicate where money is going to.

Other pairs of tables can have many more relationships between them. I've seen cases for authorization purposes and auditing, that have many FKs.

For example, the requirements stated that the app needed to record who entered the data, who verified it, who accepted it, and who executed the transaction; sometimes it even has "first-level of approval" (for amounts above US$10K) and "second-level of approval" (for amounts above $100K).

EDIT - Joining the Same Table Multiple Times

As requested, when joining the same table multiple times you need to assign different names to each "instance" of the table. Typically this is done by adding an alias to each table instance according to its role.

In this case the roles are "entered by" and "reviewed by", so the query can use the aliases e and r respectively. The query could take the form:

select o.*, e.name, r.name
from workorders o
join personnel e on e.id = o.entered_by
join personnel r on r.id = o.reviewed_by