none
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

    Monday, March 18, 2013 8:35 PM

Answers

  • 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

    • Proposed as answer by Eshani Rao Monday, March 18, 2013 11:19 PM
    • Marked as answer by Iric WenModerator Wednesday, March 27, 2013 9:09 AM
    Monday, March 18, 2013 8:41 PM