Error: Invalid Reference to from-Clause
I Have the Following Sql (Postgresql) Query: Select Ff. *, Fp. * from Fibra Ff, Fibra Fp Join Cables Cp on Fp. Cable_Id = Cp. Id Left Outer Join Terceiro...
I have the following SQL (PostgreSQL) query:
SELECT ff.*, fp.*
FROM fibra ff, fibra fp
JOIN cables cp ON fp.cable_id = cp.id
LEFT OUTER JOIN terceiro ced_pai ON ced_pai.id = cp.cedente_id
LEFT OUTER JOIN terceiro tp ON tp.id = fp.terceiro_id
JOIN cables cf ON ff.cable_id = cf.id
LEFT OUTER JOIN terceiro ced_f ON ced_f.id = cf.cedente_id
LEFT OUTER JOIN terceiro tf ON tf.id = ff.terceiro_id
where ff.fibra_pai_id = fp.id
AND ff.cable_id IN (8,9,10)
AND fp.cable_id IN (8,9,10)
But it's giving me this error:
ERROR: invalid reference to FROM-clause entry for table "ff"
LINE 8: JOIN cables cf ON ff.cable_id = cf.id
^
HINT: There is an entry for table "ff", but it cannot be referenced from this part of the query.
********** Error **********
ERROR: invalid reference to FROM-clause entry for table "ff"
SQL state: 42P01
Hint: There is an entry for table "ff", but it cannot be referenced from this part of the query.
Character: 261
Does anyone know what AM I doing wrong ?
3 Answers
You are mixing implicit and explicit JOINs. That's generally confusing to read, and leads to unexpected order-of-evaluation problems, as you've just discovered.
You should consistently use JOIN ... ON syntax everywhere; avoid the legacy FROM table1, table2. If you correct your query to use an explicit JOIN instead of FROM fibra ff, fibra fp, eg FROM fibra ff INNER JOIN fibra fp ON (ff.fibra_pai_id = fp.id) and omit ff.fibra_pai_id = fp.id from the WHERE clause, you should get the expected result.
See this question that A.H. linked to:
Convert all the joins in your query to be explicit to avoid the issue you're having--don't leave some implicit and others explicit.
This should work:
SELECT ff.*, fp.*
FROM fibra ff
JOIN fibra fp ON ff.fibra_pai_id = fp.id
JOIN cables cp ON fp.cable_id = cp.id
LEFT OUTER JOIN terceiro ced_pai ON ced_pai.id = cp.cedente_id
LEFT OUTER JOIN terceiro tp ON tp.id = fp.terceiro_id
JOIN cables cf ON ff.cable_id = cf.id
LEFT OUTER JOIN terceiro ced_f ON ced_f.id = cf.cedente_id
LEFT OUTER JOIN terceiro tf ON tf.id = ff.terceiro_id
WHERE
ff.cable_id IN (8,9,10)
AND fp.cable_id IN (8,9,10)
In my case the problem was that I forgot I had assigned my tables to a particular schema and needed to reference those e.g:
select r.*
from my_schema.resources r
Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.
