Question: Column does not exists in outer join after alias

Question

Column does not exists in outer join after alias

Answers 1
Added at 2017-09-06 17:09
Tags
Question

I have a problem with the next SQL query (generated by 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";

The resulting error is:

ERROR: column Publication.user_id does not exist

LINE 6: LEFT OUTER JOIN "user_account" AS "user" ON "Publication"."... ^

My intuition of what is happening is that "Publication"."user_id" is being aliased as userId. Then, the query is being aliased as "Publication"(overwriting the label which was previously refering to the table). The left outer join tries to match new the Publication user_id property with user.id, but it is unable because now it is called userId.

Am I correctly understanding the problem?

Answers to

Column does not exists in outer join after alias

nr: #1 dodano: 2017-09-06 17:09

ERROR: column Publication.user_id does not exist

Answer is simple user_id <> 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
Source Show
◀ Wstecz