MySQL – Left Join Not Returning Rows When Date is in Range of Longer Than 1 Day

join;MySQL

How do I left join Table A and B?

SELECT dev_hour.hour,rtdogs.delivery_date,rtdogs.delivery_hour,rtdogs.total_offer FROM
(
    SELECT hour FROM
    (
      SELECT * FROM delivery_hour
     ) xx
)dev_hour
LEFT JOIN
(
    SELECT sum(total) as 'total_offer',delivery_hour,delivery_date
                            FROM 
                            ( 
                                SELECT greatest(quan1,quan2,quan3,quan4,quan5,quan6,quan7,quan8,quan9,quan10,quan11) as total,
                                    region_id, resource_id,delivery_date,delivery_hour 
                                FROM 
                                ( 
                                    SELECT delivery_hour,delivery_date,resource_id,region_id, quan1,quan2,quan3,quan4,quan5,quan6,quan7,quan8,quan9,quan10,quan11 
                                    FROM 
                                    ( 
                                        SELECT ifnull(quantity1,0) as quan1, ifnull(quantity2,0) as quan2,ifnull(quantity3,0) as quan3,ifnull(quantity4,0) as quan4, 
                                            ifnull(quantity5,0) as quan5,ifnull(quantity6,0) as quan6,ifnull(quantity7,0) as quan7,ifnull(quantity8,0) as quan8, 
                                            ifnull(quantity9,0) as quan9,ifnull(quantity10,0) as quan10,ifnull(quantity11,0) as quan11,delivery_hour,delivery_date,resource_id,
                                            region_id 
                                        FROM rtdog  WHERE delivery_date BETWEEN '2016-01-15'  AND '2016-01-15' 
                                    )z 
                                )y  
                             )x  GROUP BY delivery_date,delivery_hour ORDER BY delivery_date
                            ) rtdogs
 ON dev_hour.hour = rtdogs.delivery_hour

I want to show all rows and null rows.
What this code is doing is when you limit the BETWEEN to 1 day it shows the null values but when it's 2 days and more it doesn't show. What's wrong with this code?

Update:
The delivery_hour table has fixed 24 rows.

Best Answer

All these nested derived tables (subselects inside subselects) are useless. You can easily remove all except 1 or 2.

SELECT 
    dev_hour.hour, 
    rtdogs.delivery_date, 
    rtdogs.delivery_hour, 
    rtdogs.total_offer 
FROM
    delivery_hour AS dev_hour
  LEFT JOIN
    ( SELECT 
          sum(greatest(ifnull(quantity1,0), ifnull(quantity2,0), ifnull(quantity3,0), 
                       ifnull(quantity4,0), ifnull(quantity5,0), ifnull(quantity6,0), 
                       ifnull(quantity7,0), ifnull(quantity8,0), ifnull(quantity9,0), 
                       ifnull(quantity10,0), ifnull(quantity11,0) ))
              AS total_offer, 
          delivery_hour, 
          delivery_date
      FROM rtdog  
      WHERE delivery_date BETWEEN '2016-01-15'  AND '2016-01-15'
      GROUP BY delivery_date, delivery_hour        
    ) AS rtdogs
  ON dev_hour.hour = rtdogs.delivery_hour ;

Further improvements:

  • The ORDER BY delivery_date is useless inside a derived table.
  • Don't use single quotes for quoting table and columns aliases (eg. 'total_offer'). Use backquotes if you have to, if the alias has spaces or other non-allowed characters. In this case, they are not needed at all.

Now the real issue of the question: the result is only 24 rows, no matter what you have in the BETWEEN condition.

The reason is that the delivery_hour table has only 24 rows (it's a guess but judging from the results, it's probably correct.) Therefore, if you want multiple dates in the results, the solution is to use another (say delivery_date) table, with the date values needed or supply one as a derived table. Then CROSS JOIN it to the delivery_hour, so you have all combinations of dates and hours that you need (say 4 days x 24 hours = 96 combinations/rows).

We can then LEFT JOIN to the rtdogs summary result as before, only changing the ON clause to take dates into account as well:

SELECT 
    dev_date.date,                         -- add the date to the result
    dev_hour.hour, 
    rtdogs.delivery_date, 
    rtdogs.delivery_hour, 
    rtdogs.total_offer 
FROM
    ( SELECT DATE('2016-01-15') AS date UNION ALL
      SELECT '2016-01-16' UNION ALL
      SELECT '2016-01-17' UNION ALL
      SELECT '2016-01-18'
    ) AS dev_date                           -- our added "dates" table
  CROSS JOIN
    delivery_hour AS dev_hour
  LEFT JOIN
    ( SELECT 
          sum(greatest(ifnull(quantity1,0), ifnull(quantity2,0), ifnull(quantity3,0), 
                       ifnull(quantity4,0), ifnull(quantity5,0), ifnull(quantity6,0), 
                       ifnull(quantity7,0), ifnull(quantity8,0), ifnull(quantity9,0), 
                       ifnull(quantity10,0), ifnull(quantity11,0) ))
              AS total_offer, 
          delivery_hour, 
          delivery_date
      FROM rtdog  
      WHERE delivery_date BETWEEN '2016-01-15'  AND '2016-01-18'
      GROUP BY delivery_date, delivery_hour        
    ) AS rtdogs
  ON  dev_date.date = rtdogs.delivery_date       -- changed here
  AND dev_hour.hour = rtdogs.delivery_hour 
ORDER BY                                         -- if we need ordering,
    date, hour ;                                 -- it's done here

You can either build the dates list dynamically (in your PHP/Java/Python code) or have a permanent "dates" table and use it. With a permanent dates table, the FROM would be like:

FROM
    dates_table AS dev_date                      -- the permanent "dates" table
  JOIN
    delivery_hour AS dev_hour
  ON dev_date.date BETWEEN '2016-01-15' AND '2016-01-18'
  LEFT JOIN
    ( SELECT 
          -- nothing changes here       
    ) AS rtdogs
  ON  dev_date.date = rtdogs.delivery_date       
  AND dev_hour.hour = rtdogs.delivery_hour 
ORDER BY                                       
    date, hour ;
Related Question