Where clause returns null, but need to return 0 Need an SQL Statement for the following:

Table a
  partID
  partNumber

Table b
  partID
  purchased

SELECT a.partNumber 
FROM a 
WHERE (SELECT SUM(b.purchased) WHERE b.partID=a.partID) &gt; 0


If the WHERE (SELECT SUM(b.purchased) WHERE b.partID=a.partID) &gt; 0 returns Null, I'll get no record to display.   Would like it to return 0 so I can show the a.partNumer having a 0 purchased quantity.

This is an MS Access database running on IIS 6.0 using ASP/VBScript
I think you need a LEFT JOIN or an inline subquery. The FROM b is also missing.

For the NULL to be converted to 0, you need the equivalent of COALESCE() or ISNULL(), which is the Nz() function in Access:

SELECT a.partNumber,
       Nz( (SELECT SUM(b.purchased) 
            FROM b
            WHERE b.partID = a.partID
           ), 0) AS sum_purchased 
FROM a ;


Assuming that table's primary (or unique) key is partID, you can have the same results with a LEFT JOIN:

SELECT a.partNumber,
       Nz(SUM(b.purchased), 0) AS sum_purchased 
FROM a 
  LEFT JOIN b
    ON b.partID = a.partID
GROUP BY a.partID, a.partNumber ;