By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 17763: ) (Hypervisor) |
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);
4 rows affected
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);
INSERT INTO payment VALUES
(1, 500), (2, 2000), (3, 1000), (3, 500), (3, 750); -- note - no payment for order_id = 4
5 rows affected
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);
| order_id | remaining |
|---|---|
| 1 | 500 |
| 3 | 750 |
| 4 | 4000 |