错误:对 FROM 子句的引用无效 [英] ERROR: invalid reference to FROM-clause

问题描述

我有以下 SQL (PostgreSQL) 查询:

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)

但它给了我这个错误:

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 ?

推荐答案

您正在混合隐式和显式 JOIN.正如您刚刚发现的那样，这通常令人困惑，并导致意想不到的求值顺序问题.

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.

你应该始终如一地使用 JOIN ... ON 语法；避免遗留的FROM table1, table2.如果您更正查询以使用显式 JOIN 而不是 FROM fibra ff, fibra fp，例如 FROM fibra ff INNER JOIN fibra fp ON (ff.fibra_pai_id = fp.id) 并在 WHERE 子句中省略 ff.fibra_pai_id = fp.id，你应该得到预期的结果.

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.

看到 A.H. 链接到的这个问题:

See this question that A.H. linked to:

混合显式和隐式连接失败，并显示表有一个条目......但是它不能从查询的这一部分引用

这篇关于错误:对 FROM 子句的引用无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！