By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table products (product_id int auto_increment primary key,
stock int)
insert into products (stock) values
(10), (20)
Records: 2 Duplicates: 0 Warnings: 0
create table line_items (id int auto_increment primary key,
order_id int,
product_id int,
quantity int)
insert into line_items (order_id, product_id, quantity) value
(1, 1, 4),
(1, 2, 3),
(2, 1, 5),
(3, 2, 8);
Records: 4 Duplicates: 0 Warnings: 0
SELECT products.product_id,
products.stock - (SELECT SUM(line_items.quantity)
FROM line_items
WHERE line_items.product_id = products.product_id
) AS stock_on_hand
FROM products
HAVING stock_on_hand > 0
| product_id | stock_on_hand |
|---|---|
| 1 | 1 |
| 2 | 9 |
SELECT p.product_id,
p.stock - l.quantity AS stock_on_hand
FROM products p
JOIN (SELECT product_id, SUM(quantity) AS quantity
FROM line_items
GROUP BY product_id) l ON l.product_id = p.product_id
HAVING stock_on_hand > 0
| product_id | stock_on_hand |
|---|---|
| 1 | 1 |
| 2 | 9 |