CODEWITHSUNDEEP
phpmysql
Milkncookiez
Milkncookiez

Reputation: 7397

Select data from 2 tables with linking attribute value of 3rd table

Table payment has as FK{exchange_order_id} the id of exchange_order and payment_info has as FK{payment_id} the id of payment. So, payment_info and exchange_order don't have any shared attribute, but I need data from them, and that's why I am trying to query them through the payment table which is the only link in-between.

I am trying to pull records for a certain time period, therefore I don't know the payment.id attribute.

I get error 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')

Upvotes: 0

Views: 36

Answers (3)

Suchit kumar
Suchit kumar

Reputation: 11859

UPDATE: first remove extra , after pi.local_market_rate as market_rate from select statement that is the main reason for the error.

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')

Upvotes: 1

Aleksa Milosevic
Aleksa Milosevic

Reputation: 133

You cant write it like that, if you write 

exchange_order eo, payment_info pi
JOIN payment p

to sql it cant know if you mean 

 (exchange_order eo, payment_info pi)
JOIN payment p

or

 exchange_order eo, (payment_info pi
JOIN payment p)

I am pretty sure you cant use both comma and join in a same FROM in sql.

Upvotes: 0

O. Jones
O. Jones

Reputation: 108696

You've got a comma between two table names in this line:

     FROM exchange_order eo, payment_info pi

Don't you need a JOIN of some kind with an ON clause of some kind?

Upvotes: 0

Related Questions