SQL Query to calculates 'reserved' inventory items

Question

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'

Answer 1

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	Reserved
1	A	2.00	0
2	B	1.00	0
3	C	1.00	0
4	D	1.00	0

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

Answer 2

Added the sql statement which considers populated column of itemkit_containers and gives correct output for reserved counts.

Query:

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;

Output:

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

Fiddle with correct reserved

Answer 3

First compute totals and reserved from the appropriate containers then left join totals and reserved to items.

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;

db-fiddle