MySQL query with sum fields from other table, with a twist-CodePudding

Sorry for the vague title, but I don't know how to word this type of problem better. Here is a simple example to explain it. I have to tables: OrderItemList and OrderHistoryLog.

OrderItemList:
|------------------------------|
| OrderNo | ItemNo | Loc | Qty |
|------------------------------|
| 100     |  A     |  1  |  1  |
| 101     |  A     |  1  |  2  |
| 102     |  A     |  1  |  1  |
| 103     |  A     |  2  |  1  |
| 104     |  A     |  2  |  1  |

OrderHistoryLog:
|------------------------------|
| OrderNo | ItemNo | Loc | Qty |
|------------------------------|
|  50     |  A     |  1  |  5  |
|  51     |  A     |  1  |  2  |
| 100     |  A     |  1  |  1  |
| 102     |  A     |  1  |  3  |
| 103     |  A     |  2  |  1  |

I need to show the records in the OrderItemList along with a LocHistQty field, which is the sum(Qty) from the OrderHistoryLog table for a given Item and Location, but only for the orders that are present in the OrderItemList.

For the above example, the result should be:

Result:
|------------------------------------------------------
| OrderNo | ItemNo | Loc | Qty | HistQty | LocHistQty |
|------------------------------|-----------------------
| 100     |  A     |  1  |  1  |   1     |     4      |
| 101     |  A     |  1  |  2  |   0     |     4      |
| 102     |  A     |  1  |  1  |   3     |     4      |
| 103     |  A     |  2  |  1  |   1     |     1      |
| 104     |  A     |  2  |  1  |   0     |     1      |

It is the last field, LocHistQty that I could use some help with. Here is what I started with (does not work):

select OI.OrderNo, OI.ItemNo, OI.Loc, OI.Qty, IFNULL(OL.Qty, 0) as HistQty, OL2.LocHistQty
from OrderItemList OI 
left join OrderItemLog OL on OL.OrderNo = OI.OrderNo and OL.ItemNo = OI.ItemNo
join 
(
    select ItemNo, Loc, sum(qty) as LocHistQty
    from zOrderItemLog 
    group by ItemNo, Loc
) as OL2 
on OL2.ItemNo = OI.ItemNo and OL2.Loc = OI.Loc 
order by OrderNo

The issue is with the above SQL is that LocHistQty contains the summary of the Qty for all orders (=11 for Loc 1 and 1 for Loc 2), not only the ones in OrderItemList.

Lastly, the real data is voluminous and query performance is important.

Help would be much appreciated.

CodePudding user response：

The subquery can join with OrderItemList to restrict the order numbers that it sums.

select OI.OrderNo, OI.ItemNo, OI.Loc, OI.Qty, IFNULL(OL.Qty, 0) as HistQty, OL2.LocHistQty
from OrderItemList OI 
left join OrderItemLog OL on OL.OrderNo = OI.OrderNo and OL.ItemNo = OI.ItemNo
join 
(
    select OL.ItemNo, OL.Loc, sum(OL.qty) as LocHistQty
    from OrderItemLog AS OL
    JOIN OrderItemList AS OI ON OL.OrderNo = OI.OrderNo
    group by OL.ItemNo, OL.Loc
) as OL2 
on OL2.ItemNo = OI.ItemNo and OL2.Loc = OI.Loc 
order by OrderNo

DEMO

CodePudding user response：

Option 1

SELECT 
    OrderNo,
    ItemNo,
    Loc,
    Qty,
    (SELECT 
            Qty
        FROM
            OrderHistoryLog AS A
        WHERE
            A.OrderNo = B.OrderNo AND A.Loc = B.Loc) AS HistQty,
    (SELECT 
            SUM(Qty)
        FROM
            OrderHistoryLog AS D
        WHERE
            D.OrderNo = B.OrderNo AND D.Loc = B.Loc) AS LocHistQty
FROM
    OrderItemList AS B;

Option 2

SELECT 
    B.OrderNo,
    B.ItemNo,
    B.Loc,
    B.Qty,
    C.Qty AS HistQty,
    (SELECT 
            SUM(Qty)
        FROM
            OrderHistoryLog AS A
        WHERE
            A.OrderNo = B.OrderNo AND A.Loc = B.Loc) AS LocHistQty
FROM
    OrderItemList AS B,
    OrderHistoryLog AS C
WHERE
    C.OrderNo = B.OrderNo AND C.Loc = B.Loc;