MySQL Date range incorrect result obtained - Query ignores the specified range

I have a query that involves searching database over a range of 30 days. Queries, both with correct output and wrong output are below:

CORRECT RESULTS:
SELECT
    affiliates.member_id,
    IFNULL( COUNT(orders.deal_id) , 0 ) AS deals_count,
    IFNULL( SUM(orders.quantity) , 0 ) AS deals_quanity
FROM affiliates
LEFT JOIN deals ON affiliates.member_id = deals.member_id
LEFT JOIN orders ON deals.deal_id = orders.deal_id
LEFT JOIN customers_orders_link ON orders.order_id = customers_orders_link.order_id
    AND DATE(customers_orders_link.datetime) BETWEEN '2011-06-01' AND '2011-07-01'
    AND customers_orders_link.order_status = 'Delivered'
GROUP BY affiliates.member_id;

EXPECTED & RECEIVED: (Correct)

MemberID    COUNT   SUM
1           11      16
2           0       0

WRONG RESULTS:

//Notice the change in the date range

SELECT
    affiliates.member_id,
    IFNULL( COUNT(orders.deal_id) , 0 ) AS开发者_StackOverflow社区 deals_count,
    IFNULL( SUM(orders.quantity) , 0 ) AS deals_quanity
FROM affiliates
LEFT JOIN deals ON affiliates.member_id = deals.member_id
LEFT JOIN orders ON deals.deal_id = orders.deal_id
LEFT JOIN customers_orders_link ON orders.order_id = customers_orders_link.order_id
    AND DATE(customers_orders_link.datetime) BETWEEN '2011-10-01' AND '2011-10-31'
    AND customers_orders_link.order_status = 'Delivered'
GROUP BY affiliates.member_id

EXPECTED:

MemberID    COUNT   SUM
1           0       0
2           0       0

BUT I RECEIVE: (INCORRECT OUTPUT)

MemberID    COUNT   SUM
1           11      16
2           0       0

The first query is producing correct results whereas the second query is producing incorrect results. Even if I use a date in the past as the range, I still receive the same Incorrect Output. Its as if the query is completely ignoring the date range specification. So this case of ignoring the date range specification seems to be the problem.

How can I make the query "see" and "obey" the date range specification and actually receive the Expected Output for the 2nd query listed above?

EDIT 1:

//Table: Orders
order_id        deal_id quantity        price
1               1       2               40.00
1               2       1               15.00
2               1       1               20.00
3               9       1               5.00
4               1       2               40.00
4               9       2               10.00
5               1       1               20.00
5               9       1               5.00
6               1       2               40.00
6               9       2               10.00
7               1       1               20.00
8               11      1               1.00


//Table: customers_orders_link
order_id        customer_id     order_status    datetime
1               4               Cancelled       2011-06-05 20:26:45
2               4               Delivered       2011-06-05 20:38:28
3               4               Pending Payment 2011-06-05 20:56:50
4               4               Pending Payment 2011-06-09 17:03:08
5               4               Pending Payment 2011-06-09 17:12:23
6               4               Pending Payment 2011-06-09 17:19:57
7               4               Pending Payment 2011-06-09 17:40:59
8               4               Pending Payment 2011-06-10 03:55:17

I solved it myself using a totally different method.

I don't know what your data looks like, but I suspect your LEFT JOIN customers_orders_link is to blame. If you only want to tally COUNT() and SUM() when the conditions of that table are met, it should be a standard JOIN in place of a LEFT JOIN.