SQL Query to calculates 'reserved' inventory items

We are creating a inventory system for items called readoutprobes and readoutprobekits. The schema, below, is simplified, using the words items and itemkits.

An itemkit, is a predefined collection of 1 or more items, i.e. a kit. In a kit, a specific type of an item, can only occur once. A kit, typically contains ~40 items. The definition of items in a kit, is captured by the itemkit_item table. The inventory for the kits, are captured in the itemkit_containers table.

An itemkit_container do not track physical item containers. Instead, its assumed that a physical itemkit is properly 'assembled', using a set of physical items, but we don't know which ones. When populated, the 'populated' field in an itemkit_containers record, is set to true.

The inventory for items are tracked by a item_containers table. Its existence is monitored by the containers volume. When the volume is 0, the container is considered emptied.

Getting the count of physical item containers, with a volume > 0, for a specific item, is obtained from the item_container table, and the same for the kits

We want to get a 'reserved count' number for each item, reflecting the kits inventory.

For example, say we got an item, named A, having a count of 42. If we are creating an itemkit containing an item named A, and a corresponding itemkit_container, we want to have a count of 'reserved' being 1, for item A.

The 'master query' for items looks like this:

SELECT items.*,         
    ic.item_count
FROM items
LEFT JOIN (
    SELECT p.id, COUNT(*) item_count, ic.item_id
    FROM  items AS p, item_containers AS ic
    WHERE p.id = ic.item_id AND ic.volume > 0
    GROUP BY p.id
    ) AS ic   
    ON ic.item_id = items.id        
GROUP BY items.id    
ORDER BY items.id;

Data in the items table:

Data in the item_containers table:

Data in the itemkits table:

Data in the itemkit_item table:

And data in the itemkit_containers:

As can be observed, the only record of an itemkit, and its inventory, contains items with item ID's = {1,3}

This question is to find out how to query for the number of 'free' (or reserved) physical items, i.e. item_containers inventory there is, at any one point in time.

The above query, returns this result:

We want an additional field, that indicate a 'Reserved' count for each item, reflecting the status of actual inventory for items and itemkits.

For the data above, this would be

A -> Reserved = 1
B -> Reserved = 0
C -> Reserved = 1
D -> Reserved = 0

A db fiddle that creates and populates the above tables is here: DB Fiddle

We are using MySQL 8.0.

NOTE: The answer below is close to correct. However, it does not relate item_containers (actual invnetory) with the itemkit_container records, but instead the itemkit records. This become clear by toggling the populated field in the itemkit_containers table to '0'. I.e.:

The output, even though the kit is no longer populated shows the same 'Reserved' count. Reserved should be equal to '0' in this case. Here is a fiddle for that case: Fiddle where Reserved should be all '0'

Solution

Thanks for such detailed description and all the necessary sample data.

As you already tried in your query you can have the item with quantity by joining items and item_containers table. For calculating free or reserved item you need to left join itemkit_containsers table since inventory for items in a kit is stored there. So just calculate the count for any item in itemkit_containers then you got your reserved quantity and by subtracting it from item_count of item_containsers table will give you free quantity for that item.

Schema and insert statements:

 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);

Query:

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

Output:

id	name	total_volume
1	A	2.00
2	B	1.00
3	C	1.00
4	D	1.00

db<fiddle here

Db-Fiddle with with populated and not populated itemkit_containsers:

Select queries (sample data):

 SELECT * from items;
 SELECT item_id, volume from item_containers;
 SELECT * FROM itemkits;
 SELECT itemkit_id, populated FROM itemkit_containers;
 SELECT * FROM itemkit_item;

Output:

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
2	Kit_2

itemkit_id	populated
1	0
2	1

itemkit_id	item_id
1	1
2	2
1	3

Query:

      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

Output:

id	name	total_volume	Reserved
1	A	2.00	0
2	B	1.00	1
3	C	1.00	0
4	D	1.00	0

db<fiddle here