mysql - 如何在两个字段上连接一个表并从两个匹配结果中检索单个列？

Question

我有两个表格，如下所示:

表1

ID, TS, Symbol, CurrencyLeft(FK Table2 Currency), CurrencyRight(FK Table2 Currency), FeePercentage

表2

ID, TS, Currency, CurrencyName

如何进行单个查询以检索 CurrencyLeft、CurrencyRight、FeePercentage、CurrencyLeftName 和 CurrencyRightName，其中 CurrencyLeftName 是 CurrencyLeft 的 CurrencyName，CurrencyRightName 是 CurrencyRight 的 CurrencyName?

Answer 1

每次要连接表时，您都可以继续添加 JOIN 语句，只要确保保持关系有序即可。例如做货币左:

SELECT m1.currencyLeft, m1.currencyRight, m1.feePercentage, m2.currencyName AS currencyLeftName
FROM table1 m1
JOIN table2 m2 ON m2.currency = m1.currencyLeft;

如果要在货币中添加对，只需再次加入table2即可:

SELECT m1.currencyLeft, m1.currencyRight, m1.feePercentage, m2.currencyName AS currencyLeftName, m3.currencyName AS currencyRightName
FROM table1 m1
JOIN table2 m2 ON m2.currency = m1.currencyLeft
JOIN table2 m3 ON m3.currency = m1.currencyRight;