LEFT JOIN with conditions

Question

I am using MS SQL. I have two tables:

1) "OrderProducts" - This table stores all the products an order has. There is another Orders main table, which we can leave out of this picture. Orders main table stores an order's main data, while the OrderProducts table stores the details.

2) "TransactionFeeProducts" - This table stores all the products' transaction fees that I want to charge for the orders. It has a BatchID, where multiple products can have the same BatchID.

Basically, all ordered products which OrderDate falls between TransactionFeeProducts.FromDate and TransactionFeeProducts.ToDate, will be charged TransactionFeeProducts.TransactionFee

OrderProducts

• ProductID
• Quantity
• OrderDate

TransactionFeeProducts

• BatchID
• ProductID
• FromDate
• ToDate
• TransactionFee

SQL:

SELECT SUM(Quantity) as Orders, 
TransactionFeeProducts.ProductID, FromDate, ToDate 
FROM TransactionFeeProducts 
LEFT JOIN OrderProducts ON TransactionFeeProducts.ProductID = OrderProducts.ProductID
WHERE
OrderDate >= TransactionFeeProducts.FromDate AND 
OrderDate <= TransactionFeeProducts.ToDate AND 
TransactionFeeProducts.BatchID = 3
GROUP BY TransactionFeeProducts.ProductID, FromDate, ToDate 
ORDER BY SUM(Quantity) DESC

I want the SQL to return ALL records in TransactionFeeProducts where the BatchID = 3. SUM(Quantity) should only give me sum where the OrderDate are made between TransactionFeeProducts.FromDate and TransactionFeeProducts.ToDate

If SUM(Quantity) is 0, then the field should be NULL or 0.

My problem now is, the SQL doesn't return any record if the SUM(Quantity) is 0.

Please help. Thank you very much.

Answer 1

Change the JOIN Condition to something like

SELECT  SUM(Quantity) as Orders,  
        TransactionFeeProducts.ProductID, 
        FromDate, 
        ToDate  
FROM    TransactionFeeProducts LEFT JOIN 
        OrderProducts   ON  TransactionFeeProducts.ProductID = OrderProducts.ProductID 
                        AND OrderDate >= TransactionFeeProducts.FromDate 
                        AND OrderDate <= TransactionFeeProducts.ToDate 
WHERE   TransactionFeeProducts.BatchID = 3 
GROUP BY    TransactionFeeProducts.ProductID, 
            FromDate, 
            ToDate  
ORDER BY    SUM(Quantity) DESC

The difference is that if you place the filtering conditions in the WHERE clause it will affect the query filtering the same as if you were to use an INNER JOIN, stating that you will only include entries from TransactionFeeProducts where OrderDate >= TransactionFeeProducts.FromDate and OrderDate <= TransactionFeeProducts.ToDate

Have a look at the following example

DECLARE @TABLE1 TABLE(
        ID INT,
        FromDate DATETIME,
        ToDate DATETIME
)

INSERT INTO @TABLE1 SELECT 1, '01 Jan 2012','31 Jan 2012'

DECLARE @TABLE2 TABLE(
        ID INT,
        DateValue DATETIME
)

INSERT INTO @TABLE2 SELECT 1, '01 Feb 2012'

SELECT  *
FROM    @TABLE1 t1 LEFT JOIN
        @TABLE2 t2  ON  t1.ID = t2.ID
                    AND t2.DateValue BETWEEN t1.FromDate AND t1.ToDate

SELECT  *
FROM    @TABLE1 t1 LEFT JOIN
        @TABLE2 t2 ON   t1.ID = t2.ID
WHERE   t2.DateValue BETWEEN t1.FromDate AND t1.ToDate

Select 1 says:

Return all rows from T1 and only those from T2 where t1.ID = t2.ID AND t2.DateValue BETWEEN t1.FromDate AND t1.ToDate

Select 2 on the other hand says:

Return all rows from T1 only those from T2 where t1.ID = t2.ID and then after that, only take rows where t2.DateValue BETWEEN t1.FromDate AND t1.ToDate

SQL SERVER – Introduction to JOINs – Basic of JOINs is always handy to have around.