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

We are creating a inventory system for items called readoutprobes and readoutprobekits. See the schema below.

A readoutprobekit, is a predefined collection of 1 or more readoutprobes, i.e. a kit. In a kit, a specific type of a readoutprobe, can only occur once.

The inventory for readoutprobes are tracked by a readoutprobe_containers table, monitoring a containers volume.

The inventory for readoutprobekits, are tracked by a readoutprobekit_containers table. However, a readoutprobekit do not track physical readoutprobe containers. Instead, its assumed that a physical readoutkit is properly 'assembled' using a set of physical readoutprobes.

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

However, we want to get a 'reserved count' number, (or 'free') for each readoutprobe, reflecting the kits that do exist, and do contain a specific readoutprobe.

For example, say we got a readoutprobe, named A, having a count of 42. Now, when creating a specific readoutprobe kit containing a readoutprobe named A, we want to have a count of 'reserved' being 1, for readoutprobe A (or free = 41).

If there is a way of adding to the query for readoutprobes and their stock, that would be preferable.

The 'master query' for readoutprobes looks like this:

SELECT readoutprobes.*,     
    v.total_volume,
    stock_containers.stock_count,
    aliquots.aliquots_count
FROM readoutprobes
LEFT JOIN (
    SELECT p.id, COUNT(*) stock_count, stock_containers.readoutprobe_id
    FROM  readoutprobes AS p, readoutprobe_containers AS stock_containers
    WHERE p.id = stock_containers.readoutprobe_id AND stock_containers.volume > 0 AND stock_containers.parent_container IS NULL 
    GROUP BY p.id
    ) AS stock_containers
    ON stock_containers.readoutprobe_id = readoutprobes.id        
LEFT JOIN (
    SELECT p.id, COUNT(*) aliquots_count, aliquot_containers.readoutprobe_id
    FROM  readoutprobes AS p, readoutprobe_containers AS aliquot_containers
    WHERE p.id = aliquot_containers.readoutprobe_id AND aliquot_containers.volume > 0 AND aliquot_containers.parent_container IS NOT NULL
    GROUP BY p.id
    ) AS aliquots    
    ON aliquots.readoutprobe_id = readoutprobes.id        
LEFT JOIN (
    SELECT readoutprobes.id, COALESCE(SUM(pt.volume),0) total_volume
    FROM readoutprobes, readoutprobe_containers AS pt
    WHERE readoutprobes.id = pt.readoutprobe_id
    GROUP BY readoutprobes.id
    ) AS v
    ON readoutprobes.id = v.id
GROUP BY readoutprobes.id    
ORDER BY readoutprobes.id;

Regarding the above query, its only aliquot containers that can be used in 'kits'.

from Recent Questions - Stack Overflow https://ift.tt/2Pv6fQ6
https://ift.tt/3aGkARf