SQL Query that calculates 'free' or 'reserved' items in a collection

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, (or 'free') for each item, reflecting the kits, and their 'items', that do exist, and do contain a specific item.

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, we want to have a count of 'reserved' being 1, for item A (or free = 41).

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 temkit_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 trying 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 'free' or 'reserved' count for each item.

For the data above, this would be

A -> Free = 0 (Reserved = 1)  B -> Free = 1 (Reserved = 0)  C -> Free = 0 (Reserved = 1)  

A script that creates and populates the above tables is here:

/*  SQLyog Ultimate v13.1.6 (64 bit)  MySQL - 8.0.21 : Database - testing  *********************************************************************  */    /*!40101 SET NAMES utf8 */;    /*!40101 SET SQL_MODE=''*/;    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;  /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;  /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;  /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;  CREATE DATABASE /*!32312 IF NOT EXISTS*/`testing` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;    USE `testing`;    /*Table structure for table `item_containers` */    DROP TABLE IF EXISTS `item_containers`;    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`),    UNIQUE KEY `Unique_1` (`item_id`),    CONSTRAINT `fk_item_tubes_readout_probes` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`)  ) ENGINE=InnoDB AUTO_INCREMENT=762 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Physical tubes received from vendor';    /*Data for the table `item_containers` */    insert  into `item_containers`(`id`,`item_id`,`volume`) values   (758,1,1.00),  (759,2,1.00),  (761,3,1.00);    /*Table structure for table `itemkit_containers` */    DROP TABLE IF EXISTS `itemkit_containers`;    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';    /*Data for the table `itemkit_containers` */    insert  into `itemkit_containers`(`id`,`itemkit_id`,`populated`) values   (1026,1,1);    /*Table structure for table `itemkit_item` */    DROP TABLE IF EXISTS `itemkit_item`;    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';    /*Data for the table `itemkit_item` */    insert  into `itemkit_item`(`itemkit_id`,`item_id`) values   (1,1),  (1,3);    /*Table structure for table `itemkits` */    DROP TABLE IF EXISTS `itemkits`;    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';    /*Data for the table `itemkits` */    insert  into `itemkits`(`id`,`name`) values   (1,'Kit_1');    /*Table structure for table `items` */    DROP TABLE IF EXISTS `items`;    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=1005 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='ReadoutProbes for mFISH Survey';    /*Data for the table `items` */    insert  into `items`(`id`,`name`) values   (1,'A'),  (2,'B'),  (3,'C');    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;  /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;  /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;  /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;  

https://stackoverflow.com/questions/67274744/sql-query-that-calculates-free-or-reserved-items-in-a-collection April 27, 2021 at 06:53AM