MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


10.6.9-MariaDB-1:10.6.9+maria~deb11


CREATE TABLE items (
id INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE inwards (
`id` INT NOT NULL AUTO_INCREMENT,
`item_id` INT NOT NULL,
`quantity` DECIMAL(8,2) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY `fk_inwards.item_id_references_items.id` (item_id)
	REFERENCES items(id)
	ON DELETE CASCADE
);
CREATE TABLE outwards (
`id` INT NOT NULL AUTO_INCREMENT,
`item_id` INT NOT NULL,
`quantity` DECIMAL(8,2) NOT NULL,
PRIMARY KEY (id),
  FOREIGN KEY `fk_outwards.item_id_references_items.id` (item_id)
	REFERENCES items(id)
	ON DELETE CASCADE
);
Insert into items (name) VALUES
("bolt"),
("wrench"),
("hammer");

SELECT * FROM items;

Records: 3  Duplicates: 0  Warnings: 0




id
name


1
bolt

2
wrench

3
hammer


INSERT INTO inwards (item_id, quantity) VALUES
(1, 10.0),
(2, 8.0);

SELECT * FROM inwards;

INSERT INTO outwards (item_id, quantity) VALUES
(1, 5.0);

SELECT * FROM outwards;

Records: 2  Duplicates: 0  Warnings: 0




id
item_id
quantity


1
1
10.00

2
2
8.00




id
item_id
quantity


1
1
5.00


SELECT it.* ,
(
COALESCE((SELECT SUM(i.quantity)
FROM inwards AS i
WHERE i.item_id = it.id),0)
-
COALESCE((SELECT SUM(o.quantity)
FROM outwards AS o
WHERE o.item_id = it.id),0)

) AS balance

FROM `items` AS it ORDER BY `id` ASC;


id
name
balance


1
bolt
5.00

2
wrench
8.00

3
hammer
0.00