postgresql : column does not exist on left join

Byadmin

Jan 3, 2022 #postgresql, #sql

postgresql : column does not exist on left join

to get the activity of the user

I have a query that left join product and its reviews and grouping them by reviews_product_id.

SELECT product_name, product_description, product_image, count, average_rating, latest_review_date, product_id, user_id
FROM products 
  LEFT JOIN (
    SELECT reviews_product_id, MAX(created_at) AS latest_review_date, COUNT(*), TRUNC(AVG(rating),1) AS average_rating 
    FROM reviews 
    GROUP BY reviews_product_id
  ) reviews on products.product_id = reviews.reviews_product_id

This is the returned rows

Result: -[ RECORD 1 ]-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
product_name        | Nescafe
product_description | nice.
product_image       | image
count               | 6
average_rating      | 3.5
latest_review_date  | 2021-12-30 14:07:11.158849+08
product_id          | 3
user_id             | 166e0efb-ee3f-47ab-b8c9-5c9b74187ec4

-[ RECORD 2 ]-------+-----------------------------------------------------------------------------

product_name        | Zesto
product_description | So Refreshing!
product_image       | image
count               | 7
average_rating      | 2.4
latest_review_date  | 2021-12-30 14:07:27.917427+08
product_id          | 2
user_id             | 166e0efb-ee3f-47ab-b8c9-5c9b74187ec4

data is correct but I want also to filter so the returned rows will output the user who made that review

SELECT product_name, product_description, product_image, count, average_rating, latest_review_date, product_id, user_id
FROM products 
  LEFT JOIN (
    SELECT reviews_product_id, MAX(created_at) AS latest_review_date, COUNT(*), TRUNC(AVG(rating),1) AS average_rating 
    FROM reviews 
    GROUP BY reviews_product_id
  ) reviews on products.product_id = reviews.reviews_product_id
WHERE reviews.user_id = '1234';