By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `items` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'oligoname + fluorophore wavelength',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='ReadoutProbes for mFISH Survey';
CREATE TABLE `item_containers` (
`id` int NOT NULL AUTO_INCREMENT,
`item_id` int NOT NULL COMMENT 'content of tube',
`volume` float(12,2) NOT NULL COMMENT 'volume in micro liter (uL)',
PRIMARY KEY (`id`),
KEY `fk_item_containers_items` (`item_id`),
CONSTRAINT `fk_item_containers_items` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=764 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Physical tubes received from vendor';
CREATE TABLE `itemkits` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
UNIQUE KEY `Unique` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1030 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='A readout kit is a collection of readouts, and defined in a codebook';
CREATE TABLE `itemkit_containers` (
`id` int NOT NULL AUTO_INCREMENT,
`itemkit_id` int NOT NULL,
`populated` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Field used for checking in checking out a tray',
PRIMARY KEY (`id`),
KEY `fk_readoutkit_tray_readoutkits` (`itemkit_id`),
CONSTRAINT `fk_readoutkit_tray_readoutkits` FOREIGN KEY (`itemkit_id`) REFERENCES `itemkits` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1027 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Physical readoutkit_tray';
CREATE TABLE `itemkit_item` (
`itemkit_id` int NOT NULL,
`item_id` int NOT NULL,
UNIQUE KEY `Uniqueness` (`itemkit_id`,`item_id`),
insert into `items`(`id`,`name`) values
(1,'A'),
(2,'B'),
(3,'C'),
(4,'D');
insert into `itemkits`(`id`,`name`) values
(1,'Kit_1');
insert into `itemkit_containers`(`itemkit_id`,`populated`) values
(1,1);
insert into `itemkit_item`(`itemkit_id`,`item_id`) values
(1,1),
(1,3);
insert into `item_containers`(`item_id`,`volume`) values
(1,1.00),
(2,1.00),
(3,1.00),
(4,1.00),
(1,1.00);
Records: 4 Duplicates: 0 Warnings: 0
Records: 2 Duplicates: 0 Warnings: 0
Records: 5 Duplicates: 0 Warnings: 0
SELECT * from items;
SELECT item_id, volume from item_containers;
SELECT * FROM itemkits;
SELECT itemkit_id, populated FROM itemkit_containers;
SELECT * FROM itemkit_item;
| id | name |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| item_id | volume |
|---|---|
| 1 | 1.00 |
| 2 | 1.00 |
| 3 | 1.00 |
| 4 | 1.00 |
| 1 | 1.00 |
| id | name |
|---|---|
| 1 | Kit_1 |
| itemkit_id | populated |
|---|---|
| 1 | 1 |
| itemkit_id | item_id |
|---|---|
| 1 | 1 |
| 1 | 3 |
SELECT items.id, items.name,
coalesce(total.v, 0) AS Total_volume,
coalesce(rsvd.v, 0) AS Reserved
FROM items
LEFT JOIN ( /* item total in item_containers */
SELECT item_id, SUM(volume) v
FROM item_containers
GROUP BY item_id
) AS total ON items.id = total.item_id
LEFT JOIN( /* item reservation by itemkit_containers */
SELECT iki.item_id, count(*) v
FROM itemkit_containers AS ic
JOIN itemkit_item AS iki
ON ic.itemkit_id = iki.itemkit_id AND ic.Populated = 1
GROUP BY iki.item_id
) AS rsvd ON items.id = rsvd.item_id
ORDER BY items.id;
| id | name | Total_volume | Reserved |
|---|---|---|---|
| 1 | A | 2.00 | 1 |
| 2 | B | 1.00 | 0 |
| 3 | C | 1.00 | 1 |
| 4 | D | 1.00 | 0 |