mysql multiple tables select last 7 days

Question

I am having 3 tables, containing some records which have a date and a numeric value (the tables can't be merged). I want to make up a bar chart using the information from the tables. The bar chart is grouped by days and should display the last seven days.

Earlier i had two tables and used the following query-scheme:

SELECT
    t.credits1,
    t.credits2,
    t.date
FROM
    (
        (
            SELECT
                t1.credits1,
                t2.credits2,
                t1.date
            FROM
                (
                    SELECT
                        SUM(credits) AS credits1,
                        date
                    FROM
                        table1
                    WHERE
                        table1.date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                    GROUP BY
                        DATE(table1.date)
                ) t1

                LEFT JOIN
                (
                    SELECT
                        SUM(credits) AS credits2,
                        date
                    FROM
                        table2
                    WHERE
                        table2.date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                    GROUP BY
                        DATE(table2.date)
                ) t2
                ON t1.date = t2.date
        )
        UNION
        (
            SELECT
                t1.credits1,
                t2.credits2,
                t1.date
            FROM
                (
                    SELECT
                        SUM(credits) AS credits1,
                        date
                    FROM
                        table1
                    WHERE
                        table1.date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                    GROUP BY
                        DATE(table1.date)
                ) t1

                RIGHT JOIN
                (
                    SELECT
                        SUM(credits) AS credits2,
                        date
                    FROM
                        table2
                    WHERE
                        table2.date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                    GROUP BY
                        DATE(table2.date)
                ) t2
                ON t1.date = t2.date
        )
    ) t GROUP BY
DATE(date)

(pseudo code)

But how can i do this with more than 2 tables? Is there any chance to set the dates of the past 7 days as a base, so that i get 7 records everytime?

To point out the problem: If I dont have records in the first table for a day, i won't get the records from the other tables for that day.

score 2 · Accepted Answer · answered Oct 06 '13 at 09:19

I assume the 3 tables have similar schemas? Try using UNION ALL to join the tables together.

SELECT ABB1.date, SUM(ABB1.credit) AS daily_total
FROM
    (SELECT date, credits
    FROM table1

    UNION ALL 

    SELECT date, credits
    FROM table1

    UNION ALL 

    SELECT date, credits
    FROM table2) AS ABB1
WHERE DATE >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)

mysql multiple tables select last 7 days

1 Answers1