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.