join on two foreign keys from same table in SQL

Question

Not quite sure how to ask this question so if someone wants to edit to better articulate please. However I want to join on a user table however the row has two FKs from the user table

item_tbl
id | ownerId | lastModifiedById | itemName
------------------------------------------
1  |       1 |                2 | "Blog Post"

user_tbl
id | username
-------------
1  |     John
2  |    Sally

Desired output (or something like it)

Owner Username | last modified by |       item
----------------------------------------------
          John |            Sally | "Blog Post"

currently i'm doing two queries to get this information. Is there a better (read: more efficient) way?

Solution 1

SELECT user_tbl.username Owner, a.username Modifier, item_tbl.itemName 
FROM item_tbl 
JOIN user_tbl 
ON item_tbl.ownerId = user_tbl.id 
JOIN user_tbl a 
ON item_tbl.lastModifiedById = a.id;

worked for those curious as hinted at by Drew in comments

Solution 2

Schema

create table user_tbl
(   id int auto_increment primary key,
    username varchar(50) not null
);

create table item_tbl
(   id int auto_increment primary key,
    ownerId int not null,
    lastModifiedById int not null,
    itemName varchar(50) not null,
    CONSTRAINT fk_own_user FOREIGN KEY (ownerId) REFERENCES user_tbl(id),
    CONSTRAINT fk_mod_user FOREIGN KEY (lastModifiedById) REFERENCES user_tbl(id)
);
insert user_tbl(username) values('John');   -- this becomes id 1
insert user_tbl(username) values('Sally');  -- this becomes id 2

Quick test for FK failure:

insert item_tbl(ownerId,lastModifiedById,itemName) values (9,9,'blah');

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails

Failed as expected, that's a good thing, because the data was bad

Success:

insert item_tbl(ownerId,lastModifiedById,itemName) values (1,2,'the Blog Post is this');

---

Query

select u1.username,u2.username,i.itemName 
from item_tbl i 
join user_tbl u1 
on u1.id=i.ownerId 
join user_tbl u2 
on u2.id=i.lastModifiedById;
+----------+----------+-----------------------+
| username | username | itemName              |
+----------+----------+-----------------------+
| John     | Sally    | the Blog Post is this |
+----------+----------+-----------------------+

Always load up on Foreign Key constraints to enforce Referential Integrity. The sign of a well-designed schema is nothing left to chance and junk being put in.

Manual page on Foreign Key Constraints.

About all that is missing is consideration for keys (indexes) added to item_tbl for the ownerId and lastModifiedById columns to make the joins extra fast and avoid table scans

Comments

[Drew]

nice job jbh, wasn't that hard

[Drew]

I think you got it slightly wrong though at the very end

[jbh]

ahhh yesss... the awesome fail copy when working with contrived data. thank you very much for the help.