CODEWITHSUNDEEP
mysqlsqljoin
Pentium10
Pentium10

Reputation: 207873

Mysql fails to Join date records, that is visible in the where clause

I have a query that looks like this:

SELECT SUM(`georder`)                                           AS `order`, 
       SUM(quantity)                                            AS quantity, 
       SUM(onhandqty)                                           AS onhandqty, 
       SUM(carryover)                                           AS carryover, 
       SUM(knownloss)                                           AS knownloss, 
       SUM(selloff1)                                            AS selloff1, 
       weekno, 
       YEAR(ardate8n)                                           AS `year`, 
       Concat(GROUP_CONCAT(DISTINCT IF(flag1<>'N', flag1, '') ORDER BY IF(flag1 
              <>'N', 
              flag1, 
              '') ASC 
              SEPARATOR ''), IF(flag1<>'N' 
                                AND b.id IS NOT NULL, 'N', '')) AS flag1, 
       Date_format(Str_to_date(Concat(Yearweek(ardate8n) + 1, ' Sunday'), 
                   '%X%V %W'), 
       '%c-%e')                                                 AS endoftheweek, 
       b.id 
FROM   sales s 
       /* 
         section starts here
      */
      LEFT JOIN blog b 
         ON s.custno = b.custno 
            AND s.prodno = b.prodno 
            AND ( DATE(s.ardate8n)>=DATE(b.start_date) 
                  AND DATE(s.ardate8n)<=DATE(b.end_date) ) 
       /* 
        section ends here
      */
       JOIN (SELECT DISTINCT `prodno` 
             FROM   `familycode` 
             WHERE  slsfamlycd = 101) f 
         ON f.prodno = s.prodno 
WHERE  s.custno = 25609 
       AND s.deptno = 0 
       AND ( DATE(ardate8n) BETWEEN '2010-11-28' AND '2011-02-26' ) 
GROUP  BY Yearweek(ardate8n) 
ORDER  BY ardate8n DESC;

This query outputs

45, 0, 146, 217, 0, 75, 09, 2011, 'N', '2-27', 46090
352, 406, 902, 551, 0, 333, 07, 2011, '', '2-20', 
352, 364, 920, 642, 0, 302, 06, 2011, '', '2-13', 
550, 477, 1830, 1465, 0, 688, 06, 2011, '', '2-6', 
968, 846, 1590, 803, 0, 540, 05, 2011, '', '1-30', 
368, 332, 1083, 808, 0, 294, 04, 2011, '', '1-23', 
449, 444, 984, 568, 0, 395, 03, 2011, '', '1-16', 
398, 441, 1090, 689, 0, 367, 02, 2011, 'H', '1-9', 
337, 337, 861, 542, 0, 282, 52, 2010, 'H', '1-2', 
254, 245, 1097, 875, 0, 325, 52, 2010, 'BH', '12-26', 
394, 398, 1024, 677, 0, 316, 51, 2010, 'B', '12-19', 
370, 395, 1066, 699, 0, 364, 50, 2010, '', '12-12', 
399, 386, 882, 528, 0, 353, 49, 2010, 'H', '12-5',

As you see there is a 'N' record in the first row.

But the corect output would be to display for the first two rows. For the weekending 2-27 and 2-20. Why doesn't read out?

If I move the date clause to the where clause I get the proper values in the above table. SQL Query used is linked here: http://paste.pocoo.org/show/348085/

And this outputs the correct two rows:

37, 0, 86, 116, 0, 56, 08, 2011, 'N', '2-27', 46089
226, 275, 501, 239, 0, 194, 08, 2011, 'N', '2-20', 46090

Table schema is given here: http://paste.pocoo.org/show/348174/

What is presented here:

  1. a Query with LEFT JOIN, that reads 1 row with 'N' flag (correct would be 2 rows)
  2. the same Query with INNER JOIN + While, that reads 2 rows with 'N' flag (correct query, but it uses INNER JOIN)

I don't understand why INNER JOIN + Where condition, doesn't show up in the LEFT JOIN if they are the same logic.

What is wrong?

Upvotes: 1

Views: 131

Answers (1)

Quassnoi
Quassnoi

Reputation: 425341

In your first query you are using LEFT JOIN which does not filter sales for the dates, rather outputting NULL values for the records that have not match in b.

As you can see in you output, b.id is NULL for every record except the first (which seems to have a match in b).

In your second query you move your join condition

( DATE(s.ardate8n)>=DATE(b.start_date) 
  AND DATE(s.ardate8n)<=DATE(b.end_date) )

into the WHERE clause.

This turns the LEFT JOIN into an implicit INNER JOIN, and the records which don't match are filtered out.

Upvotes: 2

Related Questions