I'm trying to execute a join query for 4 tables on postgres.
Table names:
scenarios_scenario
payments_invoice
payments_payment
payments_action
(all those weird names are generated by django -)))
Relations:
scenarios_scenario
[has many] payments_action
spayments_action
[has one] payments_invoice
payments_action
[has one] payments_payment
Below one is a working query,
SELECT payments_invoice.*,
(payments_payment.to_be_paid - payments_payment.paid) as remaining, \
payments_action.identificator
FROM payments_invoice
JOIN payments_payment
ON payments_invoice.action_id = payments_payment.action_id
AND payments_payment.full_payment=2
JOIN payments_action
ON payments_invoice.action_id = payments_action.id
AND payments_action.identificator = %s
I just want to retrieve a related field from another table and wrote another query like
SELECT
scenarios_scenario.title, payments_invoice.*, \
(payments_payment.to_be_paid - payments_payment.paid) as remaining, \
payments_action.identificator, payments_action.scenario_id
FROM payments_invoice
JOIN scenarios_scenario
ON scenarios_scenario.id = payments_action.scenario_id
JOIN payments_payment
ON payments_invoice.action_id = payments_payment.action_id
AND payments_payment.full_payment=2
JOIN payments_action
ON payments_invoice.action_id = payments_action.id
AND payments_action.identificator = 'EEE45667';
but facing with this error -
ERROR: missing FROM-clause entry for table "payments_action"
LINE 2: ...IN scenarios_scenario ON scenarios_scenario.id = payments_a...
^
Looked across SO for similar questions like this (missing FROM-clause entry for table) but weren't able to find a way. Any help would be appreciated.
In your first join 'payments_action' is not a known relation. Reorder your joins in a way that a new join only uses already 'defined' relations.
Here is a fiddle, demonstrating the issue:
http://sqlfiddle.com/#!17/ed147/5
Change the code so that you join each table before calling a column from it in another join. The postgres query planner reads the joins sequentially so that in your code table scenarios_scenario
is being joined to table payments_invoice
and is looking for a match with payments_action
, but the query planner doesn't know what payments_action
is yet. The new code should be:
SELECT
scenarios_scenario.title, payments_invoice.*, \
(payments_payment.to_be_paid - payments_payment.paid) as remaining, \
payments_action.identificator, payments_action.scenario_id
FROM payments_invoice
JOIN payments_action
ON (
payments_invoice.action_id = payments_action.id
AND payments_action.identificator = 'EEE45667'
)
JOIN scenarios_scenario
ON (
scenarios_scenario.id = payments_action.scenario_id
)
JOIN payments_payment
ON (
payments_invoice.action_id = payments_payment.action_id
AND payments_payment.full_payment=2
);
You are joining to table [scenarios_scenario] using a field from [payments_action].
The joins must be in sequence, i.e. you cannot reference fields from a table with the ON statement unless their tables precede the statement.
Hope that helps
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With