将一个表中的多列连接到另一表中的单列 [英] Join multiple columns from one table to single column from another table

问题描述

我正在尝试学习如何将一个表中的多列连接到另一个表中的单列.

I'm trying to learn how to join multiple columns from one table to a single column from another table.

这是我最简单的表格结构:

团队

id | team_name |
1  |   teamA   |
2  |   teamB   |
3  |   teamC   |
4  |   teamD   |

交易

id |  team_1 (FK to teams.id)  |  team_2 (FK to teams.id)  |
1  |            1              |              2            |
2  |            3              |              4            |

这是我当前将trades.team_1 连接到teams.id 的SQL:

SELECT teams.team_name AS team1, teams.team_name AS team2, trades.team_1, trades.team_2
FROM teams
JOIN trades ON (trades.team_1 = teams.id);

我的问题是，如何创建第二个连接，同时将 trades.team_2 连接到 trades.id?

这意味着trades.team_1和trades.team_2都将加入trades.id

This would mean both trades.team_1 AND trades.team_2 would be joined to trades.id

我想要返回的结果是:

team1  |  team2  |  team_1  |  team_2  |
teamA  |  teamB  |    1     |     2    |
teamC  |  teamD  |    3     |     4    |

推荐答案

像这样:

select t1.team_name as team1, t2.team_name as team2, t.team_1, t.team_2
from trades t
inner join teams t1 on t1.id = t.team_1
inner join teams t2 on t2.id = t.team_2;

这篇关于将一个表中的多列连接到另一表中的单列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！