title: MySQL - Display all dates including zero data nested join
body: <p>I'm trying to display all dates in a month, and also in the reservation detail, I only have check_in_date and check_out_date, so I have to create left join inside a left join, below is my script</p>
  
  <pre><code>SELECT
      *                   
  FROM
  (
      SELECT 
          @dt:= DATE_ADD( @dt, interval 1 day ) myDate
      FROM
      (
          SELECT 
                  @dt := '2020-01-31'
      ) vars, tb_dummy
              LIMIT 29
  ) JustDates 
  LEFT JOIN
  (
      SELECT 
          DATE_FORMAT(d.myDate2,'%Y-%m-%d') AS `myDate2`,
          COALESCE(count(rdt.reservation_detail_id), 0) AS `RNS`,
          FORMAT(SUM(rdt.subtotal_amount/COALESCE(DATEDIFF(DATE(DATE(rdt.check_out_date)), DATE(rdt.check_in_date)), 0)), 2) AS `REVENUE`,
          FORMAT(SUM(rdt.subtotal_amount/COALESCE(DATEDIFF(DATE(DATE(rdt.check_out_date)), DATE(rdt.check_in_date)), 0))/COALESCE(count(rdt.reservation_detail_id), 0), 2) AS `AVGREV`        
      FROM
      (
          SELECT 
              @dt:= DATE_ADD( @dt, interval 1 day ) myDate2
          FROM
          (
              SELECT 
                      @dt := '2020-01-31'
          ) vars2, tb_dummy
              LIMIT 29
      ) d
      LEFT JOIN 
          tb_reservation_detail rdt 
      ON d.myDate2 BETWEEN DATE(rdt.check_in_date) AND DATE(DATE(rdt.check_out_date) - INTERVAL 1 DAY)
      INNER JOIN 
          tb_reservation R 
      ON rdt.reservation_id = R.reservation_id
      WHERE 
          rdt.reservation_status_id &lt;&gt; 3
      AND
          R.property_id = 57
      GROUP BY d.myDate2
      ORDER BY d.myDate2 ASC
  )   Resv
      ON
          JustDates.myDate = Resv.myDate2
  ORDER BY
      JustDates.myDate ASC
  </code></pre>
  
  <p>when i run it only return dates from the left table like : <a href="https://i.stack.imgur.com/o9LTS.png" rel="nofollow noreferrer">Left join result</a></p>
  
  <p>but when I change </p>
  
  <pre><code>SELECT
      *                   
  FROM
  (
      SELECT 
          @dt:= DATE_ADD( @dt, interval 1 day ) myDate
      FROM
      (
          SELECT 
                  @dt := '2020-01-31'
      ) vars, tb_dummy
              LIMIT 29
  ) JustDates 
  **LEFT JOIN**
  (
  </code></pre>
  
  <p>to </p>
  
  <pre><code>SELECT
      *                   
  FROM
  (
      SELECT 
          @dt:= DATE_ADD( @dt, interval 1 day ) myDate
      FROM
      (
          SELECT 
                  @dt := '2020-01-31'
      ) vars, tb_dummy
              LIMIT 29
  ) JustDates 
  **RIGHT JOIN**
  (
  </code></pre>
  
  <p>it returns data from the right table like this: <a href="https://i.stack.imgur.com/M4Yig.png" rel="nofollow noreferrer">Right join result</a></p>
  
  <p>What is wrong with my code? </p>