SELECT in SQL Server to show all distinct rows from the tables OnHand, Sale and Purchase that have either/or Qty field not empty

Question

I need to write a SELECT query in SQL Server which uses a JOIN or UNION that selects distinct ItmNo or Code rows from 3 tables OnHand, Sale and Purchase.

Here are the details of the tables I have and what I need. ItmNo and/or Code columns can be used as foreign keys to join the tables.

These are my input tables-

Table OnHand

ID           ItmNo     Code   Qty
----------------------------------
1            I001      001    100
2            I001      001     50
3            I003      003    300

Table Sale

ID          ItmNo     Code   Qty
----------------------------------
1           I001      001    100
2           I004      004    
3           I003      003    120

Table Purchase

ID          ItmNo     Code   Qty
----------------------------------
1           I005      005     10
2           I003      003    200
3           I003      003    300

And this is what I need as output. Only DISTINCT ItmNo and Code should be displayed here:

ID          ItmNo     Code   SumQtyOnHand    SumQtyOnSale    SumQtyOnPurchase
------------------------------------------------------------------------------
1           I001      001    150             100
2           I003      003    300             120             500
3           I005      005                                     10

Here is the SELECT query that I have tried is below but I cannot get the output I want-

SELECT 
    A.ItmNo, A.Code,
    A2.TOTAL SumQtyOnHand,
    B.TOTAL SumQtyOnSale,
    C.TOTAL SumQtyOnPurchase
FROM
    dbo.OnHand A
LEFT JOIN 
    (SELECT ItmNo, Code, SUM(Qty) TOTAL
     FROM dbo.OnHand 
     GROUP BY ItmNo, Code) A2 ON A.ItmNo = A2.ItmNo
LEFT JOIN 
    (SELECT ItmNo, Code, SUM(Qty) TOTAL
     FROM dbo.Sale
     GROUP BY ItmNo, Code) B ON A.ItmNo = A2.ItmNo
LEFT JOIN 
    (SELECT ItmNo, Code, SUM(Qty) TOTAL
     FROM dbo.Purchase
     GROUP BY ItmNo, Code) C ON A.ItmNo = A2.ItmNo

Please suggest the correction in the SELECT query to achieve the above output.

Thanks in advance!

This was very helpful. Thanks! What if I have a couple of columns in OnHand that are not present in other tables Sale and Purchase? How can I select them in the query above with union all? @GMB — Shivam Santosh, Jan 15 '21 at 18:29

score 0 · Accepted Answer · answered Nov 17 '20 at 20:08

I think you are on the right track with the prea-ggregation subqueries. Then, you can full join. The syntax is a bit cumbersome in SQL Server, that does not support the using() clause:

select 
    coalesce(o.itmno, s.itemno, p.itemno) as itemno,
    coalesce(o.code,  s.code,   p.code) as code,
    o.SumQtyOnHand,
    s.SumQtyOnSale,
    p.SumQtyOnPurchase
from ( 
    select itmno, code, sum(qty) SumQtyOnHand
    from dbo.onhand 
    group by itmno, code
) o
full join (
    select itmno, code, sum(qty) SumQtyOnSale
    from dbo.sale
    group by itmno, code
) s on s.itmno = o.itmno and s.code = o.code
full join ( 
    select itmno, code, sum(qty) SumQtyOnPurchase
    from dbo.purchase
    group by itmno, code
) p on p.itemno = coalesce(s.itemno, o.itemno) and p.code = coalesce(s.code, o.code)

It might be simpler expressed with union all and aggregation:

select itemno, code, 
    sum(qtyOnHand) as SumQtyOnHand, 
    sum(qtyOnSale) as SumQtyOnSale, 
    sum(qtyOnPurchase) as SumQtyOnPurchase
from (
    select itemno, code, qty as qtyOnHand, null as qtyOnSale, null as qtyOnPurchase from dbo.onhand
    union all select itemno, code, null, qty, null from dbo.sale
    union all select itemno, code, null, null, qty from dbo.purchase
) t
group by itemno, code

This was very helpful. Thanks! What if I have a couple of columns in OnHand that are not present in other tables Sale and Purchase? How can I select them in the query above with union all? @GMB — Shivam Santosh, Dec 09 '20 at 14:22

SELECT in SQL Server to show all distinct rows from the tables OnHand, Sale and Purchase that have either/or Qty field not empty

1 Answers1