表payment
有FK{exchange_order_id}
有exchange_order
的id, payment_info
有FK{payment_id}
有payment
的id。所以,payment_info和exchange_order没有任何共享属性,但我需要来自它们的数据,这就是为什么我试图通过payment表来查询它们,这是两者之间唯一的链接。
我正在尝试拉某个时间段的记录,因此我不知道payment.id
属性。
我得到错误near 'FROM exchange_order eo, payment_info pi JOIN payment p ON p.exchange_order_id = ' at line 6
SELECT
eo.amount_proposed as proposed,
eo.amount_realized as realized,
pi.local_fee as fee,
pi.local_market_rate as market_rate,
FROM exchange_order eo, payment_info pi
JOIN payment p ON p.exchange_order_id = eo.id
JOIN payment_info ON p.id = pi.payment_id
WHERE create_time >= UNIX_TIMESTAMP('2015-09-01') AND create_time < UNIX_TIMESTAMP('2015-10-01')
UPDATE:首先从select语句中删除pi.local_market_rate as market_rate
之后额外的,
,这是导致错误的主要原因。
SELECT
eo.amount_proposed as proposed,
eo.amount_realized as realized,
pi.local_fee as fee,
pi.local_market_rate as market_rate
FROM exchange_order eo
JOIN payment p ON p.exchange_order_id = eo.id
JOIN payment_info pi ON p.id = pi.payment_id
WHERE create_time >= UNIX_TIMESTAMP('2015-09-01') AND create_time < UNIX_TIMESTAMP('2015-10-01')
这行中两个表名之间有一个逗号:
FROM exchange_order eo, payment_info pi
难道你不需要一个带有ON子句的JOIN吗?
你不能这样写,如果你写
exchange_order eo, payment_info pi
JOIN payment p
to SQL,它无法知道你是否指的是
(exchange_order eo, payment_info pi)
JOIN payment p
或
exchange_order eo, (payment_info pi
JOIN payment p)
我很确定你不能同时使用逗号和连接在同一个FROM sql