T-SQL Query not bringing back a count of 0

Question

Hi, I have a feeling I am making some sort of foolish mistake here, however I am trying to do a query over two tables. One table contains the value I want to aggregate over, in this case I have called if the StoreCharge table. The other table contains the values I want to count.

SELECT  StoreCharge.StoreId,
        COUNT(DISTINCT(ISNULL(WholesalerInvoice.WholesalerId,0))) AS Invoices
FROM    StoreCharge 
            LEFT OUTER JOIN
            WholesalerInvoice ON StoreCharge.StoreId = WholesalerInvoice.StoreId
WHERE   StoreCharge.CompanyId = 2
AND         WholesalerInvoice.StoreInvoiceId IS NULL
AND         DATEDIFF(day,WholesalerInvoice.InvoiceDate,'20100627') > =0
AND     DATEDIFF(day,dateadd(day,-7,'20100627'),WholesalerInvoice.InvoiceDate) > 0
GROUP BY    StoreCharge.StoreId

My problem is that if there are rows in the counting table that match the WHERE clause, the query works ok. However When no rows match the criteria nothing is returned instead of a list of the values in StoreCharge with a count of 0.

Answer 1

Based on the query in the example, you don't actually use what you join in. Unless there is more to the query a Subquery would produce the desired result.

SELECT  StoreCharge.StoreId,
        (SELECT COUNT(0) FROM WholesalerInvoice WHERE WholesalerInvoice.StoreId = StoreCharge.StoreId
        AND DATEDIFF(day,WholesalerInvoice.InvoiceDate,'20100627') > =0
        AND DATEDIFF(day,dateadd(day,-7,'20100627'),WholesalerInvoice.InvoiceDate) > 0) [Invoices]
FROM    StoreCharge 
WHERE   StoreCharge.CompanyId = 2

Answer 2

WHERE is evaluated after the LEFT OUTER JOIN

Try moving your WHERE filter related to WholesalerInvoice into the OUTER JOIN

SELECT  StoreCharge.StoreId, 
        COUNT(DISTINCT(ISNULL(WholesalerInvoice.WholesalerId,0))) AS Invoices 
FROM    StoreCharge  
    LEFT OUTER JOIN 
    WholesalerInvoice ON StoreCharge.StoreId = WholesalerInvoice.StoreId 
AND DATEDIFF(day,WholesalerInvoice.InvoiceDate,'20100627') > =0 
AND DATEDIFF(day,dateadd(day,-7,'20100627'),WholesalerInvoice.InvoiceDate) > 0 

WHERE   StoreCharge.CompanyId = 2 
GROUP BY    StoreCharge.StoreId

This will filter the required WholesalerInvoice records out and leave the StoreCharge table intact.