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) > 0

If the WHERE (SELECT SUM(b.purchased) WHERE b.partID=a.partID) > 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

1

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.