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

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

*Is it okay to have two foreign keys in one table pointing to the same table's primary key?* Yes. This situation is common enough (sender-receiver, homeplayer-guest, etc.) — Akina, Feb 11 '22 at 19:31
Please in code questions give a [mre]--cut & paste & runnable code & example input; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debug fundamentals.) — philipxy, Feb 24 '22 at 20:16
Content re your post belongs in a comment. But not one that's redundant, like asking to reopen. [help] You can research & ask (if researched & on-topic) re asking & your post via Q&A at [meta] & [meta.se]--but beware of "the meta effect". My last comment gives a summary of [mre] but you don't do many parts, like the 1st. PS Ask 1 specific researched non-duplicate question. What's yours? It's unclear what the question mark sentence asks (SELECTing doesn't use FKs) & it isn't the post title question or the implicit question "how to put them into a single query". Joining a table twice is a faq. — philipxy, Mar 28 '22 at 22:21
Tables (bases, views & query results) represent relation(ship)s/associations. FK constraints are often called "relation(ship)s" but they are not. They say that subrows appear elsewhere as a PK/UNIQUE. Table meanings are necessary & sufficient to query. Constraints--including CKs, PKs & FKs-- are not needed to query. They are consequences of the table relation(ship)/association choices & what situations/states can arise. They are for integrity to be enforced by the DBMS. (But when constraints hold, additional queries return the same results as queries that don't assume constraints.) — philipxy, Mar 28 '22 at 22:22
[Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) [How to get matching data from another SQL table for two different columns: Inner Join and/or Union?](https://stackoverflow.com/q/27682228/3404097) [What is a self join for? (in english)](https://stackoverflow.com/a/37384306/3404097) — philipxy, Mar 28 '22 at 22:24

The Impaler · Accepted Answer · 2022-02-11T21:17:05.437

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

@agerber85 Please edit the question and add a few rows of sample data and the expected result. — The Impaler, Feb 14 '22 at 18:53
@agerber85 Yes, that makes sense. If you wanted to include all the rows, even the ones with nulls. then replace both `join` by `left join` in my query. Of course, `e.name` and/or `r.name` will show up as null since there are no matches. — The Impaler, Feb 14 '22 at 20:42

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

1 Answers1