Query help

Question

Item                                                                     Groups

ItemID     Qty                                                    GroupId  ItemId  Qty   

1              10                                                      100         1          2

                                                                          101         1          2

                                                                          102         1          5

2              4                                                        103          2          5

I want to write a query that returns the following resultset

ItemId GroupId Qty

1            100       2

1            101       2

1            102       5

1            NULL     1

2            103       4

The qty in the Items table is the available qty. I need to join Items with Groups to get the groupids having sum of qty <= qty in items.

                                                      

---

Anonymous

Answer 1

Try

SELECT * FROM (select I.ItemId, G.GroupId, G.Qty

from Item I INNER JOIN Groups G ON I.ItemID = G.ItemId

UNION ALL

select I.ItemId, NULL AS GroupId, I.Qty -  COALESCE(SUM(G.Qty),0) as Qty

from Item I LEFT JOIN Groups G ON I.ItemID = G.ItemId

GROUP BY I.ItemID, I.Qty

HAVING I.Qty > COALESCE(SUM(G.Qty),0)) X ORDER BY ItemId, GroupId

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog