Good Morning all, I am having a problem understanding a simple LATERAL join - I'm working on grasping them. All tables and data are at the bottom of this question and on the fiddles, SQL Server (working) and Postgres (not working). SQL Server fiddle - https://dbfiddle.uk/hjBBd87B Postgres fiddle - https://dbfiddle.uk/PihnqTwG I have the following - create table scripts and sample data are at end of this question and on the fiddles. The query in question which works on SQL Server is the following: SELECT o.order_id, o.total_price - COALESCE(p.paid, 0) AS remaining FROM _order o CROSS APPLY ( SELECT SUM(p.amount) AS paid FROM payment p WHERE p.order_id = o.order_id ) AS p WHERE o.total_price > ISNULL(p.paid, 0); relatively simple one would have thought! I tried to convert this into a Postgres query as follows: SELECT o.order_id, o.total_price - COALESCE(sub.paid, 0) FROM _order o LEFT JOIN LATERAL ( SELECT SUM(p.amount) AS paid FROM payment p WHERE p.order_id = o.order_id ) AS sub WHERE o.total_price > ISNULL(sub.paid, 0); -- << line 10 - Error occurs! but I receive the error: ERROR: syntax error at or near "WHERE" LINE 10: WHERE o.total_price > ISNULL(sub.paid, 0); I would be grateful if I could get a working query and also for an explanation as to what it is I'm doing incorrectly? The payment table has no primary key. Any input on the suitability of creating a surrogate one would also be appreciated. Please let me know if there's any important information missing. Rgs and TIA, E. CREATE TABLE _order ( order_id INT NOT NULL PRIMARY KEY, total_price INT NOT NULL ); INSERT INTO _order VALUES (1, 1000), (2, 2000), (3, 3000), (4, 4000); CREATE TABLE payment ( order_id INT NOT NULL, amount INT NOT NULL, CONSTRAINT payment_order_id_fk FOREIGN KEY (order_id) REFERENCES _order (order_id) ); CREATE INDEX pt_order_id_ix ON payment (order_id); -- normal indexing of foreign key field INSERT INTO payment VALUES (1, 500), (2, 2000), (3, 1000), (3, 500), (3, 750); -- note - no payment for order_id = 4
