Difference of two tables 2

[dnagahawatte]

Hi All;
Say i have two tables. PartsTable1, PartsTable2
PartsTable1 has two columns (PartNo, Qty)
PartsTable2 also have two columns (PartNo, Qty)
PartsTable1
-----------
PartNo Qty
558 - 5
658 - 6
788 - 10

PartsTable2
PartNo Qty
558 - 10
658 - 8
788 - 10
999 - 5
1025 - 1
----------------------
How can i find the difference in part number of two tables.Basically PartsTable1.Qty -PartsTable2.qty.
When i do the above i get NULL values for PartNo 999 and 1025.
Answer i need is ---------
PartNo Qty
558 - 5
658 - 2
788 - 0
999 - 5
1025 - 1
----------------------------
Can somebody pls help

 

[SQLSister]

look at the isnull function or coalesce in Books online

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[gmmastros]

Select PartNo, 
       IsNull(PartsTable2.QTY, 0) - IsNull(PartsTable1.QTY) As NewQuantity
From   PartsTable1
       Left Join PartsTable2 
         On PartsTable1.PartNo = PartsTable2.PartNo

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jschaddock]

Use isnull, that lets you set the value to zero if it returns as null. I've used an outer join below because I'm not sure what you want to do if there's a record in t2 but not t1, you can easily change this to a left join if you want

select abs(isnull(t1.Qty, 0) - isnull(t2.Qty, 0)) as Result
from PartsTable1 t1
full outer PartsTable2 t2
on t1.PartNo = t2.PartNo

 

[dnagahawatte]

Thanks All.