title: how to convert an empty record to zero
body: <p>I am working on a Point-of-sale system, but I have been stranded lately.  I have two tables from which I want to pick data, but some of the values I retrieve are <code>NULL</code>s, whereas I need them to be zeros.  This happens when there is a row in one table (<code>[dbo.ProdDetails]</code>) without a corresponding row in the other table <code>[dbo.Pro_Sales]</code>.</p>
  
  <p>I am using this query:</p>
  
  <pre><code> SELECT a.ItemCODE,a.OpenStock,c.UnitsSold
   FROM
     (SELECT  x.ItemCODE,x.OpenStock FROM  dbo.ProdDetails x) a
     LEFT OUTER JOIN
  
     (SELECT  x.ProductID,ISNULL(CONVERT(varchar(50),x.Quantity),'') UnitsSold
      FROM  dbo.Pro_Sales  x
      GROUP BY x.ProductID,x.Quantity
      ) c
   ON a.ItemCODE=c.ProductID
  
   WHERE a.ItemCODE ='0005'
  </code></pre>
  
  <p>The result am getting is</p>
  
  <p>itemCode  OpenStock UnitsSold
  0005       6        NULL</p>