CODEWITHSUNDEEP
sqldatabasepostgresqljoinsyntax-error
marmeladze
marmeladze

Reputation: 6572

Postgres: missing FROM-clause entry for table

I'm trying to execute a join query for 4 tables on postgres.

Table names:

(all those weird names are generated by django -)))

Relations:

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" error for a rails table query) but weren't able to find a way. Any help would be appreciated.

Upvotes: 20

Views: 99960

Answers (5)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

I got the same error below:

ERROR: missing FROM-clause entry for table "my"

When I didn't use a current_setting() to use a custom option as shown below:

SELECT my.num;

So, I used current_setting() as shown below, then I could use the custom option without error:

SELECT current_setting('my.num');

Upvotes: 0

ADM-IT
ADM-IT

Reputation: 4200

I had similar error message but related to alias naming issue. 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" // added quotes
select a_dr.address_line1 from core_addresses as "a_dr" // snake case

Upvotes: 1

Laposhasú Acsa
Laposhasú Acsa

Reputation: 1580

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

Upvotes: 28

Bryan Schwimmer
Bryan Schwimmer

Reputation: 115

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

Upvotes: 7

stubs
stubs

Reputation: 264

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

Upvotes: 5

Related Questions