PostgreSQL: How to use LATERAL JOIN to derive values for multiple rows in the same query?
I need help solving I'm maintaining legacy code that I've looked through the documentation and I'm still confused about I'm trying to use a LATERAL join in PostgreSQL to derive values from a set of rows in a subquery for each row in the main query... The goal is to calculate the total price of orders from a 'products' table based on the 'order_items' table, but I'm running into issues with the syntax and performance. Here's a simplified version of my tables: ```sql CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(100), price NUMERIC ); CREATE TABLE order_items ( item_id SERIAL PRIMARY KEY, order_id INT, product_id INT, quantity INT, FOREIGN KEY (product_id) REFERENCES products (product_id) ); ``` And I want to execute a query like this: ```sql SELECT o.order_id, total_price FROM orders o JOIN LATERAL ( SELECT SUM(p.price * oi.quantity) AS total_price FROM order_items oi JOIN products p ON oi.product_id = p.product_id WHERE oi.order_id = o.order_id ) AS derived ON true; ``` However, I'm getting an behavior message: `behavior: column "o.order_id" does not exist`. It seems like the lateral join isn't correctly referencing the outer query. I've tried changing the structure of the join and using CTEs, but I still face the same scenario. Additionally, I'm concerned about performance because I expect orders to be numerous. Could someone explain how to properly implement this LATERAL join or suggest an alternative approach to calculate total prices for each order efficiently? Is there a best practice when dealing with similar scenarios in PostgreSQL? For context: I'm using Sql on Debian. Is there a better approach? This issue appeared after updating to Sql 3.10. What would be the recommended way to handle this? What would be the recommended way to handle this? What's the best practice here?