ERROR: invalid reference to FROM-clause
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_idWHERE 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