Mysql多次连接两个表

Question

我试图从同一个表中获取两个用户名和公司名称，其中一个是

gamer_user where u.id  = gamer_rec t2.user_id

其中 gamer_user 具有用户名，gamer_rec 具有公司名称

SELECT t1.company,usc.`name`,ur.id,u.id,t1.user_name,t2.user_name
FROM `game_social` AS usc 
JOIN gamer_rec t2 ON t2.user_id = `usc`.`user_id` 
JOIN gamer_rec t1 ON t1.user_id = `u`.`id` 
JOIN `gamer_user` AS u ON `usc`.`user_id` = `u`.`id`  
JOIN `gamer_user` AS ur ON `ur`.`email` = `usc`.`email`

但我收到错误

[Err] 1054 - Unknown column 'u.id' in 'on clause'

gamer_rec t1 或 t2

user_id user_name
1          paul
2          thomas
3          Nancy

gamer_user u 或 ur

id    user_id  email
1      2       eabce@abce.com
2      1       aabc@abc.com
3      4       dabcd@abcd.com

游戏社交

id  user_id   email
1    2       aabc@abc.com
2    3       dabcd@abcd.com
3    1       eabce@abce.com
4    6       fabcf@abcf.com

这是输出..请找到 t1.user_id 和 t2.user_id --- 但我需要 t2.user_id = ur.id and t1.user_id= u.id是一样的

Answer 1

您需要加入表才能使用它。因此切换连接:

SELECT t1.company, t1.user_name, 
       t2.user_name,
       usc.`name`,
       ur.id, 
       u.id

FROM game_social usc 

JOIN gamer_user u ON `usc`.`user_id` = `u`.`id`
JOIN gamer_user ur ON `ur`.`email` = `usc`.`email`

JOIN gamer_rec t1 ON t1.user_id = `u`.`id` 
JOIN gamer_rec t2 ON t2.user_id = `usc`.`user_id`