Postgres: missing FROM-clause entry for table

user_4481312 0
Viewed 78689

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_actions
  • payments_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.

postgresql sql
4 Answers

I had similar issue but related to alias naming. For example the following sql query does not work and throws the same error missing FROM-clause entry for table:

select aDr.address_line1 from core_addresses as "aDr"

If you are using Capital letters in alias names, you must use double quotes or use snake_case instead. The following queries fix the issue:

select "aDr".address_line1 from core_addresses as "aDr"
select a_dr.address_line1 from core_addresses as "a_dr"
user_11433490
Related