mysql - 在 MySQL 中连接两个表或选择查询

Question

我需要连接两个表并获得如下所示的输出，

这里我的问题是我无法内部连接两个表并获取输出，因为它引用 team1 和 team2 的相同列名称(这看起来像“select ct.teamname,ct2.teamname from clm_schedule cs”，其中它给了我一个错误)。我可以在两个单独的查询中完成它，如下所示，

select ct.teamname as team1 from clm_schedule cs inner join clm_team ct on ct.teamid = cs.team1

select ct2.teamname as team2 from clm_schedule cs2 inner join clm_team ct2 on ct2.teamid = cs2.team2

但我无法加入它来获取单个结果集。请给我建议一个方法。

Answer 1

你应该能够做到这一点:

SELECT ct1.teamname as team1 ,ct2.teamname as team2 
FROM clm_schedule cs 
LEFT JOIN clm_team AS ct1 
  ON ct1.teamid = cs.team1
LEFT JOIN clm_team AS ct2
  ON ct2.teamid = cs.team2;