Complicated query that compares subselects

Question

I have pretty hard query to write and I have no idea how to approach this.

I also have no idea for my question title, so if someone has good idea how to describe my problem - please suggest it.

---

I'm working on software for stock management and I need a query that will show all differences between "Expected groups" and "Inventoried group".

There is inventory_item_stocktakings table that holds data about Stocktaking. It is related with inventory_item_groups table, which is a table used to group inventory_items for many different purposes. Each "Stocktaking" can be made of multiple inventory_item_stocktakings records. For example this is a "Stocktaking" performed on 2 source groups (39, 40) with "Expected items" and there is one result group (41) for "Inventoried items".

To get differences between "Expected items" and "Inventoried items" I have written this query:

select * from
    (select inventory_item_group_id, inventory_item_id from inventory_item_group_items where inventory_item_group_id in (39, 40)) e
RIGHT JOIN 
    (select inventory_item_group_id, inventory_item_id from inventory_item_group_items where inventory_item_group_id in (41)) i
on e.inventory_item_id = i.inventory_item_id
where e.inventory_item_id is null
UNION 
select * from
    (select inventory_item_group_id, inventory_item_id from inventory_item_group_items where inventory_item_group_id in (39, 40)) e
LEFT JOIN 
    (select inventory_item_group_id, inventory_item_id from inventory_item_group_items where inventory_item_group_id in (41)) i
on e.inventory_item_id = i.inventory_item_id
where i.inventory_item_id is null

This is the result "Stocktaking" performed on 39 and 40 grops, with results stored in 41 group and there are 6 missing items and 2 unexpected items.

However this cannot be parametrized (notice hardcoded 39, 40 and 41 group id's).

How can extend this query to show all differences from all existing stoctakings?

Answer 1

If you think of a Venn diagram, you are fetching the items that in one table and not the other?

I see no advantage, and a significant performance disadvantage, in having that query the extra layer of SELECTs.

SELECT * FROM (
    SELECT ...
        FROM a
        RIGHT JOIN b ON ...
        WHERE ... ) ab

can be simplified to

 ( SELECT ...
        FROM a
        RIGHT JOIN b ON ...
        WHERE ... ) ab

I don't know if the UNION can be eliminated, but it can be sped up by changing from UNION DISTINCT (the default) to UNION ALL.

More

I think this is equivalent, after shortening the names:

SELECT  e.x, e.id, i.x, i.id
    FROM        items e  ON  e.id = i.id  AND  e.x in (39, 40)
    RIGHT JOIN  items i  ON  e.id = i.id  AND  i.x in (41)
    where  e.id is null
UNION 
SELECT  e.x, e.id, i.x, i.id
    FROM        items e  ON  e.id = i.id  AND  e.x in (39, 40)
    LEFT JOIN   items i  ON  e.id = i.id  AND  i.x in (41)
    WHERE  i.id is null

But perhaps you did not want the NULLs to show, so (changing only the SELECT list):

SELECT  i.x, i.id 
    FROM        items e  ON  e.id = i.id  AND  e.x in (39, 40)
    RIGHT JOIN  items i  ON  e.id = i.id  AND  i.x in (41)
    where  e.id is null
UNION 
SELECT  e.x, e.id
    FROM        items e  ON  e.id = i.id  AND  e.x in (39, 40)
    LEFT JOIN   items i  ON  e.id = i.id  AND  i.x in (41)
    WHERE  i.id is null

(I never use RIGHT JOIN because my head does not turn around like an owl's, so I don't know if I am using it properly.)

Answer 2

So I managed to get the hardcoded numbers from a subquery with join, but I'm not sure if this is correct.

I am not sure about this subquery, if it is correctly joined:

-- ...
SELECT
    expected_items_group_id
FROM
    inventory_stocktakings s2
where
    s2.expected_items_group_id = esub.inventory_item_group_id
)
-- ...

And here is full query:

select
    *
from
    (
    select
        inventory_item_group_id,
        inventory_item_id
    from
        inventory_item_group_items esub
    where
        inventory_item_group_id in (
        SELECT
            expected_items_group_id
        FROM
            inventory_stocktakings s2
        where
            s2.expected_items_group_id = esub.inventory_item_group_id
        )) e
LEFT JOIN 
    (
    select
        inventory_item_group_id,
        inventory_item_id
    from
        inventory_item_group_items isub
    where
        inventory_item_group_id in (
        SELECT
            inventoried_items_group_id
        FROM
            inventory_stocktakings s3
        where
            s3.inventoried_items_group_id = isub.inventory_item_group_id)) i
on
    e.inventory_item_id = i.inventory_item_id
where i.inventory_item_id is null
UNION ALL
select
    *
from
    (
    select
        inventory_item_group_id,
        inventory_item_id
    from
        inventory_item_group_items esub
    where
        inventory_item_group_id in (
        SELECT
            expected_items_group_id
        FROM
            inventory_stocktakings s2
        where
            s2.expected_items_group_id = esub.inventory_item_group_id
        )) e
RIGHT JOIN 
    (
    select
        inventory_item_group_id,
        inventory_item_id
    from
        inventory_item_group_items isub
    where
        inventory_item_group_id in (
        SELECT
            inventoried_items_group_id
        FROM
            inventory_stocktakings s3
        where
            s3.inventoried_items_group_id = isub.inventory_item_group_id)) i
on
    e.inventory_item_id = i.inventory_item_id
where e.inventory_item_id is null

So far, with single inventory_stocktakings record pair it works the same as with hardcoded numbers.

Now I have to add my inventory_stocktakings to the final result set.