MySQL 如何根据主表中的值从另一个表中选择两个附加列？

How to select two additional columns from another table based on values in the main table?

提问by CP1985

I have a table named maintablewith 3 columns: id, userid1and userid2.

我有一个以maintable3 列命名的表：id,userid1和userid2.

Another table named usersis keyed by userid, and has nameas a column.

另一个名为的表以users为键userid，并name作为一列。

I want to select something along the lines of:

我想选择以下内容：

SELECT maintable.*, users.name AS username1, users.name AS username2 
FROM maintable, users 
WHERE ...

Basically I want to get all the columns in the maintablerow, and add two columns at the end that will draw the names for userid1and userid2from the userstable.

基本上我想获取行中的所有列maintable，并在最后添加两列，以便为表格绘制名称userid1和userid2从users表格中提取名称。

I'm unsure how to format the where clause for a query like this.

我不确定如何为这样的查询格式化 where 子句。

采纳答案by Pablo Santa Cruz

You need to join twice with users:

您需要与用户加入两次：

SELECT m.*, u1.name, u2.name
FROM maintable m 
INNER JOIN users u1 ON (m.userid1 = u1.userid)
INNER JOIN users u2 ON (m.userid2 = u2.userid)

You can read the documentation about MySQL JOIN Syntax here.

您可以在此处阅读有关 MySQL JOIN 语法的文档。

回答by Paulraj

something like this,

像这样的东西，

select m.*,
(select u1.name from users as u1 where m.userid1 = u1.userid) as username1,
(select u2.name from users as u2 where m.userid2 = u2.userid) as username2
from 
maintable as m