列不存在于别名之后的外部连接中

Question

我与下一个SQL查询有问题（由sequelize orm生成）：

SELECT 
    "Publication".*, 
    "user"."id" AS "user.id"
FROM 
    (SELECT "Publication"."club_id" AS "clubId", "Publication"."user_id" AS "userId" FROM "publication" AS "Publication" WHERE ("Publication"."club_id" = '1')) AS "Publication" 
    LEFT OUTER JOIN "user_account" AS "user" ON "Publication"."user_id" = "user"."id";

结果错误是：

错误：列publication.user_id不存在

第6行：左外部加入" user_account"作为"出版物"上的"用户"。 ^

我对发生的事情的直觉是，"Publication"."user_id"被别名为userId。然后，查询被称为 "Publication"（覆盖以前指的表格的标签）。左外的联接试图将新的Publication user_id属性与user.id匹配，但它是无法匹配的，但是现在称为userId。

我是否正确理解了问题？

Answer 1

错误：列 publication.user_id 不存在

答案是简单的user_id＆lt;> userId：

SELECT "Publication".*, 
       "user"."id" AS "user.id"
FROM 
    (SELECT "Publication"."club_id"  AS "clubId"
            ,"Publication"."user_id" AS "userId" 
      FROM "publication" AS "Publication" 
     WHERE "Publication"."club_id" = '1') AS "Publication" 
LEFT JOIN "user_account" AS "user" 
  ON "Publication"."userId" = "user"."id";
                    -- here use column's alias