从SQL中的同一表联接两个外键 [英] join on two foreign keys from same table in SQL

问题描述

不太清楚如何问这个问题，因此，如果有人想进行编辑以更好地表达自己的意思，请.但是我想加入一个用户表，但是该行有两个来自用户表的FK

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?

推荐答案

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;

为Drew在评论中暗示的好奇心服务

worked for those curious as hinted at by Drew in comments

这篇关于从SQL中的同一表联接两个外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！