If result of subquery is > 1, don't use other results-CodePudding

I'm working on building this SQL function that is a lot more complex than what I'm usually used to, and I could really use some community eyes to bounce this off of. It's a question that is hard to word, so please bear with me.

I have a data table of items in a kit. Let's say my kit has 10 items, and my kit is on back order, because 1 item doesn't have enough stock for that kit.

I have a report that shows all kits that can be taken off of back order if the items can fill a kit.

My query was returning all back ordered kits, because it's returning all of the items still with enough stock to fill the kit. So, it would return 9 of the 10 items and report that it can be taken off of back order, which is wrong.

With how the data is structured and linked, sadly the back order status is linked to the kit, not the item stock. So, I need some sort of SQL query that can see every item in the kit, can see if every item can fill the kit, and only return the result if all items are in stock.

I basically need to have two sub-queries that can compare the results. So I was thinking in pseudo code:

SELECT [needed fields]
FROM kits k
JOIN items i USING (k.itemid)
IF (subquery 1 results that finds properly backed ordered kits) 
IS NOT IN (subquery 2 results that finds all items with enough stock for a kit)
THEN results are good to send back to report.

I'm trying my best to keep this all SQL, and I'm going to keep working on this after the question is posted.

Okay, since people wanted some sort of tangible SQL, here's the complex web I'm working with. fair warning, there is a lot of nomenclature and names that don't match up with my simplified example above.

This query below is returning the one material properly marked in a partial.

SELECT DISTINCT p.jobid, p.partialid, p.quantity, p.holdstamp, fn_normalize_desc(p.description) AS pdesc, fn_normalize_desc(j.description) AS jdesc,
                    printdate, DATE(duedate) AS duedate, fn_get_partial_status(p.partialid) AS status, c.client, i.materialid, m.matcode, m.quantity AS mquant
                    FROM partials p
                    JOIN jobs j USING (jobid)
                    JOIN clients c USING (clientid)
                    LEFT JOIN insertion_guides i
                    ON p.partialid = i.partialid
                    LEFT JOIN materials m
                    ON i.materialid = m.materialid
                    LEFT JOIN componentdata_mailgroups g
                    ON p.partialid = g.partialid
                    LEFT JOIN componentdata_groups d
                    ON p.partialid = d.partialid
                    WHERE p.quantity > m.quantity AND p.complete_date IS NULL AND g.maildate IS NULL AND fn_get_partial_status(p.partialid) = 'Waiting for Materials' AND d.discarded IS NULL
        HAVING COUNT(p.quantity < m.quantity AND p.complete_date IS NULL AND g.maildate IS NULL AND fn_get_partial_status(p.partialid) = 'Waiting for Materials' AND d.discarded IS NULL) > 0;

So my second step I'm trying to do, is compare the results of this query #1 above, to my query #2 that returns a list of partials that can be taken off hold, and exclude the results of query #1.

CodePudding user response：

Count up all your item stock, keep the ones that are zero, and use that to filter out kits that join to any record. Something like this:

Select k.kitid 
From kits k Left Outer Join (
    Select itemid from items
    Group By itemid
    Having sum(itemquantity) <= 0
) z on k.itemid=z.itemid
Group By k.kitid
Having count(z.itemid)=0

You did not describe your tables or their elements so the inner query will need to be adjusted to fit the actual representation of inventory.

You are counting up the number of out of stock items in each kit and only keeping the ones that have none.

CodePudding user response：

so this is my answer that worked for me, but definitely feel free to critique for efficiency and edit formatting.

SELECT DISTINCT
    p.jobid,
    p.partialid,
    p.quantity,
    p.holdstamp,
    fn_normalize_desc(p.description) AS pdesc,
    fn_normalize_desc(j.description) AS jdesc,
    p.printdate,
    DATE(p.duedate) AS duedate,
    fn_get_partial_status(p.partialid) as status, c.client,
    i.materialid,
    m.matcode,
    m.quantity AS mquant
FROM partials p
JOIN jobs j USING(jobid)
JOIN clients c USING(clientid)
LEFT JOIN insertion_guides i
ON p.partialid = i.partialid
LEFT JOIN materials m
ON i.materialid = m.materialid
LEFT JOIN componentdata_mailgroups g
ON p.partialid = g.partialid
LEFT JOIN componentdata_groups d
ON p.partialid = d.partialid
LEFT JOIN (
    SELECT
        p.jobid,
        p.partialid,
        p.quantity,
        p.holdstamp,
        fn_normalize_desc(p.description) AS pdesc,
        fn_normalize_desc(j.description) AS jdesc,
        p.printdate,
        DATE(p.duedate) AS duedate,
        fn_get_partial_status(p.partialid) as status,
        c.client,
        i.materialid,
        m.matcode,
        m.quantity AS mquant
    FROM partials p
    JOIN jobs j USING(jobid)
    JOIN clients c USING(clientid)
    LEFT JOIN insertion_guides i
    ON p.partialid = i.partialid
    LEFT JOIN materials m
    ON i.materialid = m.materialid
    LEFT JOIN componentdata_mailgroups g
    ON p.partialid = g.partialid
    LEFT JOIN componentdata_groups d
    ON p.partialid = d.partialid
    WHERE p.quantity > m.quantity AND p.complete_date IS NULL AND g.maildate IS NULL AND fn_get_partial_status(p.partialid) = 'Waiting for Materials' AND d.discarded IS NULL
    HAVING
        COUNT(
            p.quantity < m.quantity AND p.complete_date IS NULL AND g.maildate IS NULL AND fn_get_partial_status(p.partialid) = 'Waiting for Materials' AND d.discarded IS NULL
        ) > 0
) b ON i.partialid = b.partialid
WHERE p.quantity < m.quantity AND p.complete_date IS NULL AND g.maildate IS NULL AND fn_get_partial_status(p.partialid) = 'Waiting for Materials' AND d.discarded IS NULL AND b.partialid IS NULL

I used a mix of Chris's answer, with this answer here: Building a query by excluding the results of another query

I compare the sub query results vs the results of the query I want, and exclude the sub query results.