JOIN连接表时报FROM子句条目引用错误，SQL查询问题排查

你遇到的报错是SQL语法中显式JOIN与隐式逗号连接的优先级差异导致的：

• SQL语法规则里，显式JOIN的运算优先级高于逗号分隔的隐式表连接。你当前写的FROM res_partner p, account_move_line l, account_account JOIN res_country as rc on (rc.id = p.country_id) 逻辑上会被数据库优先解析为account_account JOIN res_country ，这一阶段的查询作用域内还不存在p这个表别名，因此触发了无法引用表p的报错。

推荐使用全显式JOIN的规范写法，彻底规避优先级问题，修改后的语句如下：

SELECT DISTINCT p.id AS id, rc.code as country_code, p.name AS name, 
(select up.name from res_partner up where up.id = (select u.partner_id from res_users u where u.id = p.user_id)) as user, p.ref as ref
FROM res_partner p
JOIN account_move_line l ON l.partner_id = p.id
JOIN account_account ON l.account_id = account_account.id
JOIN res_country as rc on rc.id = p.country_id
WHERE 
    account_account.company_id = 1
    AND account_account.reconcile = True
ORDER BY p.name

如果你不想修改原有隐式连接的写法，也可以把前面三个隐式连接的表用括号包裹，提升它们的运算优先级，不过该写法可读性较差不推荐：

FROM (res_partner p, account_move_line l, account_account)
JOIN res_country as rc on (rc.id = p.country_id) 

内容的提问来源于stack exchange，提问作者Chaban33