I have a table named maintable with 3 columns: id, userid1 and userid2.
我有一个名为maintable的表，有3列:id、userid1和userid2。
Another table named users is keyed by userid, and has name as a column.
另一个名为users的表由userid进行键控，并将名称作为列。
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 maintable row, and add two columns at the end that will draw the names for userid1 and userid2 from the users table.
基本上，我希望获得主表行的所有列，并在末尾添加两个列，它们将从users表中绘制userid1和userid2的名称。
I'm unsure how to format the where clause for a query like this.
我不确定如何为这样的查询格式化where子句。
2 个解决方案
#1
16
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连接语法的文档。
#2
4
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
#1
16
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连接语法的文档。
#2
4
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