How to have SQL INNER JOIN accept null results
Instead of doing an INNER join, you should do a LEFT OUTER join:
SELECT a.CLIENT_ID_MD5, COUNT(a.CLIENT_ID_MD5) TOTAL, ISNULL(c.FIRM,'') FROM dbo.amazonlogs a LEFT OUTER JOIN dbo.customers c ON c.CLIENT_ID_MD5 = a.CLIENT_ID_MD5GROUP BY a.CLIENT_ID_MD5, c.FIRM ORDER BY COUNT(0) DESC