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,0);
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);
select i.id,i.name,sum(ic.volume) as total_volume,
sum(coalesce(ii.item_count,0)) as Reserved
from items i inner join item_containers ic on i.id=ic.item_id
left join (select item_id,count(*) as item_count from itemkit_containers ic
inner join itemkit_item i on ic.itemkit_id =i.itemkit_id and ic.populated=1
group by item_id) ii
on i.id=ii.item_id
group by i.id,i.name
order by i.id,i.name
| id | name | total_volume | Reserved |
|---|---|---|---|
| 1 | A | 2.00 | 0 |
| 2 | B | 1.00 | 0 |
| 3 | C | 1.00 | 0 |
| 4 | D | 1.00 | 0 |