时间戳

首页 > 解决方案 > 尝试加入表时从子句条目错误中获取

sql - 尝试加入表时从子句条目错误中获取

问题描述

这是我的查询，但我收到此错误

ValueError: "invalid reference to FROM-clause entry for table "p"
LINE 5:             JOIN res_country as rc on (rc.id = p.country_id)...
                                                       ^
HINT:  There is an entry for table "p", but it cannot be referenced from this part of the query.



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, account_move_line l, account_account
            JOIN res_country as rc on (rc.id = p.country_id) 
            WHERE l.account_id = account_account.id
                AND l.partner_id = p.id
                AND account_account.company_id = 1
                
                AND account_account.reconcile = True
                
            ORDER BY p.name

我的查询有什么问题？

标签： sqlpostgresql

解决方案

您正在混合过时的隐式连接和“现代”显式 JOIN 运算符。这总是一个坏主意，也是您错误的主要原因，因为两者的评估规则不同。

如果您只使用显式 JOIN 运算符，您还将立即看到您缺少account_account表之间的连接条件

...
FROM res_partner p
  JOIN account_account ON ?????
  JOIN account_move_line l 
    ON l.account_id = account_account.id
   AND l.partner_id = p.id
  JOIN res_country as rc on (rc.id = p.country_id) 
WHERE account_account.company_id = 1
  AND account_account.reconcile = True

推荐阅读