在不同的列上两次加入同一个表 [英] joining the same table twice on different columns

问题描述

我有一个 user 表和一个 complaint 表.

I've got a user table and a complaint table.

complaint 表的结构如下:

[opened_by]   [complaint_text]   [closed_by]
 (user_id)         (text)         (user_id)
 (user_id)         (text)         (user_id)
 (user_id)         (text)         (user_id)

所有用户，包括 complainers 和 complaint-resolvers 都位于表 user 中.

All users, both the complainers and complaint-resolvers are located in table user.

如何编写查询以显示两列的用户名?

How do I write a query to show the username for both columns?

这给了我一个:

SELECT user.username, complaint.complaint_text
FROM complaint
LEFT JOIN user ON user.user_id=complaint.opened_by

但我不知道怎么写，所以 _by 列都显示用户名而不是 ID.

but I don't know how to write it so both _by columns show usernames rather than IDs.

推荐答案

SELECT 
     complaint.complaint_text, 
     A.username, 
     B.username
FROM 
     complaint 
     LEFT JOIN user A ON A.user_id=complaint.opened_by 
     LEFT JOIN user B ON B.user_id=complaint.closed_by

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