Postgres SQL query doesn't identify the column

Question

I'm having a huge problem with my query, for some reason I just can't get one of the WHERE clauses to work.

This is my SQL:

SELECT COUNT(*) FROM "diets" JOIN "meals" on "idDiet" = "dietId" 
WHERE kcal != 0 AND "diets.createdAt" > '2016-10-2' 
GROUP BY "userIdUser" HAVING count(*) >= 5;

And my error:

ERROR:  column "diets.createdAt" does not exist

My scheme for both tables:

Any idea on what I must do for this query to work? Thank you very much, if more information is needed please let me know.

Answer 1

Your quotes are wrong:

SELECT COUNT(*)
FROM "diets" JOIN
     "meals" 
     ON "idDiet" = "dietId" 
WHERE kcal <> 0 AND "diets"."createdAt" > '2016-10-2' 
GROUP BY "userIdUser"
HAVING count(*) >= 5;

The double quotes go around an identifier. A qualified column reference such as diets.createdAt consists of two identifiers, so each needs to have the quotes (if you have them at all).

Otherwise, you are referring to a column whose name is "diets.createdAt". That is, the column name would have a period in it.

Answer 2

 SELECT COUNT(*) FROM diets a  JOIN  meals b on a.idDiet = b.dietId 
 WHERE a.kcal <> 0 AND a.createdAt > '2016-10-2' 
 GROUP BY a.userIdUser HAVING count(*) >= 5;