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

which can be created and populated thus:

CREATE TABLE orders(
workorder int(10) unsigned NOT NULL AUTO_INCREMENT,
entered_by int(10) unsigned NOT NULL,
reviewed_by int(10) unsigned NOT NULL, 
PRIMARY KEY (workorder),
FOREIGN KEY (entered_by) REFERENCES personnel(id),
FOREIGN KEY (reviewed_by) REFERENCES personnel(id)
);
ALTER TABLE orders AUTO_INCREMENT = 1;
INSERT INTO personnel(name) VALUES('John Smith');
INSERT INTO personnel(name) VALUES('John Adams');
INSERT INTO personnel(name) VALUES('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

which can be created and populated thus:

CREATE TABLE orders(
workorder int(10) unsigned NOT NULL AUTO_INCREMENT,
entered_by int(10) unsigned NOT NULL,
reviewed_by int(10) unsigned NOT NULL, 
PRIMARY KEY (workorder),
FOREIGN KEY (entered_by) REFERENCES personnel(id),
FOREIGN KEY (reviewed_by) REFERENCES personnel(id)
);
INSERT INTO orders(entered_by, reviewed_by) VALUES (2,3);
INSERT INTO orders(entered_by, reviewed_by) VALUES (3,1);

I know how to

SELECT workorder, personnel.name AS entered
FROM orders JOIN personnel
ON personnel.id = orders.entered_by
ORDER BY orders.workorder;

which results in

workorder	entered
1	John Adams
2	Samuel Adams

and how to

SELECT workorder, personnel.name AS entered
FROM orders JOIN personnel
ON personnel.id = orders.entered_by
ORDER BY orders.workorder;

which yields:

workorder	reviewed
1	Samuel Adams
2	John Smith

but I'm not sure how to put them into a single query (that I can use in a query form in Base), so that it will display:

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
You seem to be editing your old posts to improve them. Unfortunately, you didn't make [mre]s to pin down your problems (obligatory in debug questions) & (partly from not having problems pinned down) you didn't research them so they are duplicates of very old very asked very answered beginner questions. These are both reasons to close & to downvote & not upvote. Even your edit here has not given a [mre] even though I suggested it months ago when you asked it. You still aren't saying clearly how the result is a function of input, "put them into a single query" says almost nothing. — philipxy, Jul 16 '22 at 05:46
Thanks, philipxy! I need all the help I can get in getting my act together on how I post questions. This isn't really a debug question so much as a hypothetical question, and I thought the code I posted was exactly what was needed to create and understand that hypothetical situation. — agerber85, Jul 18 '22 at 12:36

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