postgresql 错误：对 FROM 子句的引用无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13297491/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ERROR: invalid reference to FROM-clause
提问by lcguida
I have the following SQL (PostgreSQL) query:
我有以下 SQL (PostgreSQL) 查询：
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 ?
有谁知道我做错了什么？
回答by Craig Ringer
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。正如您刚刚发现的那样，这通常令人困惑，并且会导致意想不到的求值顺序问题。
You should consistently use JOIN ... ONsyntax 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.idfrom the WHEREclause, you should get the expected result.
您应该始终如一地使用JOIN ... ON语法；避免遗留问题FROM table1, table2。如果您更正您的查询以使用显式 JOIN 而不是FROM fibra ff, fibra fp, egFROM fibra ff INNER JOIN fibra fp ON (ff.fibra_pai_id = fp.id)并ff.fibra_pai_id = fp.id从WHERE子句中省略，您应该得到预期的结果。
See this question that A.H. linked to:
请参阅 AH 链接到的这个问题：
回答by Ray
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)
