add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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);


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;

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 GROUP BY item_id
) item_in_kit
ON items.id = item_in_kit.item_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 1
2 B 2 1 2 1.00 0
3 C 3 1 3 1.00 1
4 D 4 1 4 1.00 0