关于MySQL联接的帮助

Question

我正在建立一个监测货币价格的系统。我数据库结构的一部分如下所示：

我的系统试图通过以下查询为每个交换提取api代码：

SELECT distinct E.exchange_id, E.exchange_name, P.pair_id, P.pair_name,T.pair_ticker           
FROM exchange as E
left Join exchange_has_currency_pairs as PE on E.exchange_id = PE.exchange_exchange_id
left Join currency_pairs as P on PE.currency_pairs_pair_id = P.pair_id
left join currency_pairs_ticker as T on P.pair_id = T.currency_ticker_id
order by E.exchange_name desc

查询将返回所有结果，但在所有情况下，pair_ticker都不匹配对名或对应的exchange。应该在currency_pairs到currency_pairs_ticker上再加上n:m，还是我的联接不正确？

谢谢你的意见。

从下面的答案中编辑输出样本

+-------------+---------------+---------+-----------+-----------------------------------------------------------------------+
| exchange_id | exchange_name | pair_id | pair_name | pair_ticker                                                                |
+-------------+---------------+---------+-----------+------------------------------------------------------------------------+
|           4 | bitstamp      |       1 | btc_usd   | https://btc-e.com/api/2/btc_usd/ticker                                 |
|           4 | bitstamp      |       1 | btc_usd   | https://coinbase.com/api/v1/prices/spot_rate?currency=USD              |
|           4 | bitstamp      |       1 | btc_usd   | https://www.bitstamp.net/api/ticker/                                   |
|           4 | bitstamp      |       1 | btc_usd   | http://pubapi.cryptsy.com/api.php?method=singlemarketdata&marketid=2   |
|           1 | btc-e         |       1 | btc_usd   | https://btc-e.com/api/2/btc_usd/ticker                                 |
|           1 | btc-e         |       1 | btc_usd   | https://coinbase.com/api/v1/prices/spot_rate?currency=USD              |
|           1 | btc-e         |       1 | btc_usd   | https://www.bitstamp.net/api/ticker/                                   |

Answer 1

看起来您的currency_pairs_ticker加入了错误的字段。

你现有的加入：

left join currency_pairs_ticker as T on P.pair_id = T.currency_ticker_id

修正连接：

left join currency_pairs_ticker as T on P.pair_id = T.currency_pairs_pair_id

完整的sql

SELECT distinct E.exchange_id, E.exchange_name, P.pair_id,    P.pair_name,T.pair_ticker           
FROM exchange as E
left Join exchange_has_currency_pairs as PE on E.exchange_id = PE.exchange_exchange_id
left Join currency_pairs as P on PE.currency_pairs_pair_id = P.pair_id
left join currency_pairs_ticker as T on P.pair_id = T.currency_pairs_pair_id
order by E.exchange_name desc