MySQL - Display all dates including zero data nested join

Question

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

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 <> 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

when i run it only return dates from the left table like : Left join result

but when I change

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**
(

to

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**
(

it returns data from the right table like this: Right join result

What is wrong with my code?

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Mar 12 '20 at 20:05

score 0 · Accepted Answer · answered Mar 12 '20 at 20:38

welcome to StackOverflow. I think your problem is that you don't quite understand the difference between RIGHT JOIN and LEFT JOIN. Check out this StackOverflow post that goes over the differences.

As far as wanting to display all of the dates in a month, here's a link to an answer I posted that I believe does what you want it to. In my answer I provide an example query that contains a derived table you can select from and then LEFT JOIN your tables to so it will show all the days in the month regardless if there is data in your tables for a given day or not.

Hope this helps.

MySQL - Display all dates including zero data nested join

1 Answers1