0

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.

  • Do you mean that, when it's 2 days or more, the query returns rows but there are no NULLs among them? Do you mean you want your output to have each date in the BETWEEN range, whereas it actually has only dates that are present in the source table(s)? – Andriy M Jan 20 '16 at 6:58
  • This is what I want. I have table A consists of 24 rows. Table B consists of thousands rows. WHen I query for date range. What I want is all 24 rows will be shown (left join) even with a null rows in Table B if I use BETWEEN. Null values were not shown – PHP Noob Jan 20 '16 at 7:19
4

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 ;                  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.