Mysql – Full outer join in MySql

group byMySQLsubquery

I'm on MySQL trying to get the user's daily post count grouped by post category.

So for every day in a given date range, with no gap, I need the post count for each existing post category for a given user. When the user has not posted anything in a category for that day, that category must still be listed with a null or, better yet, 0 postCount.

I've solved the daily date part by using a sequence table. But I'm failing to include the empty categories. I believe I need to emulate full outer join with union, but I'm not sure where to start.

My current query:

select 
    `list_of_dates`.`date` as `date`, 
    `post_categories`.`name` as `categoryName`, 
    count(`posts`.`id`) as postCount 
from ( 
    SELECT 
        DATE_ADD('2016-12-28', INTERVAL seq.seq DAY) AS date 
    FROM 
        seq_0_to_999 AS seq 
    WHERE 
        DATE_ADD('2016-12-28', INTERVAL seq.seq DAY) <= '2017-03-28' 
) as list_of_dates 
left join 
    ( 
        SELECT `posts`.* FROM posts WHERE `posts`.user_id = 65 
    ) as posts on DATE(`posts`.`created_at`) = `date`
left join 
    `post_categories` on `post_categories`.`id` = `posts`.`category_id` 
group by 
    `date`, `categoryName` 
order by 
    `date` asc

Best Answer

You need to first CROSS JOIN the two tables, list_of_dates and post_categories and then LEFT JOIN posts:

SELECT 
    ld.date     AS date, 
    pc.name     AS categoryName, 
    COUNT(p.id) AS postCount 
FROM 
    ( SELECT 
          DATE_ADD('2016-12-28', INTERVAL seq.seq DAY) AS date 
      FROM 
          seq_0_to_999 AS seq 
      WHERE 
          DATE_ADD('2016-12-28', INTERVAL seq.seq DAY) <= '2017-03-28' 
    ) AS ld                   -- list_of_dates 
CROSS JOIN
    post_categories AS pc     -- list_of_categories 
LEFT JOIN 
    posts AS p
  ON  p.user_id = 65 
  AND p.created_at >= ld.date AND p.created_at < ld.date + INTERVAL 1 DAY
  AND p.category_id = pc.id 
GROUP BY 
    ld.date, 
    pc.id, pc.name 
ORDER BY
    ld.date ASC ;
Related Question