MySQL 8.0

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`),
  KEY `fk_readoutkit_item_readout_probes` (`item_id`),
  CONSTRAINT `fk_readoutkit_item_readout_probes` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  CONSTRAINT `fk_readoutkit_item_readoutkits` FOREIGN KEY (`itemkit_id`) REFERENCES `itemkits` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='associations table for definition of a readout kit';


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 * from items;
SELECT item_id, volume from item_containers;
SELECT * FROM itemkits;
SELECT itemkit_id, populated FROM itemkit_containers;
SELECT * FROM itemkit_item;

SELECT items.*,         
    ic.*,
    v.total_volume,
    COALESCE(item_in_kit.item_count,0) AS Reserved
FROM items
LEFT JOIN (
    SELECT i.id, COUNT(*) item_count, ic.item_id
    FROM  items AS i, item_containers AS ic
    WHERE i.id = ic.item_id AND ic.volume > 0
    GROUP BY i.id
    ) AS ic   
    ON ic.item_id = items.id        
    
LEFT JOIN (
    SELECT items.id, COALESCE(SUM(ic.volume),0) total_volume
    FROM items, item_containers AS ic
    WHERE items.id = ic.item_id
    GROUP BY items.id
    ) AS v
    ON items.id = v.id
    
LEFT JOIN item_containers 
    ON item_containers.item_id = items.id
    
LEFT JOIN (
    SELECT item_id, COUNT(*) AS item_count 
    FROM itemkit_item where itemkit_id not in
    (select itemkit_id from itemkit_containers where populated = 0)
    GROUP BY item_id
    ) item_in_kit
    ON items.id = item_in_kit.item_id        
    
GROUP BY items.id    
ORDER BY items.id;


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
0




itemkit_id
item_id


1
1

1
3




id
name
id
item_count
item_id
total_volume
Reserved


1
A
1
2
1
2.00
0

2
B
2
1
2
1.00
0

3
C
3
1
3
1.00
0

4
D
4
1
4
1.00
0