Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community!

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Vincent Lee
Vincent Lee
13,156 Points
Posted by Vincent Lee
Vincent Lee
13,156 Points

MySQL query results from two tables, grouped by date.

Here are my two tables and my desired query result: alt text

Here's the two SELECT statements. I've tried combining them these two but can't get it to work.

    SELECT
        date AS t,
        COUNT(*) AS likes
    FROM likes
    GROUP BY t

    SELECT
        date AS t,
        COUNT(*) AS comments
    FROM comments
    GROUP BY t

Once I've got them combined, I think I'll need to fill in the empty fields with 0s. Also I'm not sure how I can get days where there are zero likes and comments to show up.

3 Answers

Vincent Lee
Vincent Lee
13,156 Points
Vincent Lee
Vincent Lee
13,156 Points

Finally managed to do it! Thought I'd post here if anyone is looking for something similar in the future.

Created the table date and populated it with dates. Then LEFT JOINed both the likes and comments table. Made sure to use IFNULL functions to turn any NULLs into 0s.

SELECT
    date.date,
    IFNULL(likes,0) AS likes,
    IFNULL(comments,0) AS comments
FROM `date`
LEFT JOIN (SELECT
                date AS t,
                COUNT(*) AS likes
            FROM `likes`
            GROUP BY t) likes
ON date.date = likes.t
LEFT JOIN (SELECT
                date AS t,
                COUNT(*) AS comments
            FROM `comments`
            GROUP BY t) comments
ON date.date = comments.t
Andrew Shook
Andrew Shook
31,709 Points
Andrew Shook
Andrew Shook
31,709 Points

Vincent, if it's possible I would change the database structure a little bit. I would give comments a unique auto incrementing ID column in their table and then have each like store the ID of the comment they belong to. It would then be easier to query for comments and their corresponding likes. That being said I believe you need to preform an inner join on the two tables, but I'm not sure you can since you don't have unique values to join on.

Vincent Lee
Vincent Lee
13,156 Points
Vincent Lee
Vincent Lee
13,156 Points

Thanks for the reply Andrew. I'll take a look into the inner join on the two tables. Would this make sense? Having a third table with just dates and making that column the unique values, then joining the other tables on to it.