在同一张桌子上多次加入 [英] Joining on the same table more than once

问题描述

我可能有点傻，希望有人能帮忙.

I'm probably being a bit dumb, hopefully someone can help.

我有一个简单的2列用户表(ID，USERNAME).
我有一张图片注释表(COMMENT，COMMENTFROM，COMMENTTO)

I have a simple 2 column user table (ID, USERNAME).
I have a comments table for images (COMMENT, COMMENTFROM, COMMENTTO)

COMMENTFROM是发表评论的用户的ID. COMMENTTO是添加了评论的图像的所有者的ID.这两个用户都保存在USERS表中.

COMMENTFROM is the ID of the user who made the comment. COMMENTTO is the ID of the owner of the image that the comment was added to. Both users are held within the USERS table.

我想拉出并显示这样的行

I want to pull out and display rows like this

"really nice photo" - to USERXYZ - from USER123**

这使我感到困惑，因为如果我将USERS表加入到以下内容的评论表中:

This has puzzled me, because if I join the USERS table to the comments table on:

WHERE comments.userfrom = users.id

那只会让我每行需要的2个用户名中的一个(或另一个).有什么办法可以让我都得到的吗?

That only gets me one (or the other) of the 2 usernames I need per row. Is there a way I can get both?

我什至不知道如何在SOF上搜索此答案，如果之前已经回答过，则表示歉意.如果有人能指出我正确的方向，将不胜感激:)

I'm not even sure how I would search for this answer on SOF, apologies if it has been answered before. If anyone can point me in the right direction it would be appreciated :)

推荐答案

您需要两次JOIN到users表，并在每个JOIN在您的SQL中.

You need to JOIN to the users table twice, and give them different identifiers (aka aliases) on each JOIN within your SQL.

SELECT 
    comment, 
    userFrom.username AS commentFrom,
    userTo.username AS commentTo
FROM comments 
JOIN users AS userFrom ON userFrom.ID = comment.commentFrom
JOIN users AS userTo ON userTo.ID = comment.commentTo

这篇关于在同一张桌子上多次加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！