Looking to fill in the blanks with NULLs when there isn't a record that is for a row that I am expecting. Consider the following wp_posts (p) ID post_title 1 Week 1 2 Week 2 3 Week 3 4 Week 4 5 Week 5 wp_users (u) ID user_email 1 email1@example.com 2 email2@example.com 3 email3@example.com 4 email4@example.com 5 email5@example.com wp_learndash_user_activity (lua)

MySQL to fill in the blanks with NULLs when their isn’t a row based on set number of IDs from the join

Looking to fill in the blanks with NULLs when there isn’t a record that is for a row that I am expecting.

Consider the following

wp_posts (p)

ID	post_title
1	Week 1
2	Week 2
3	Week 3
4	Week 4
5	Week 5

wp_users (u)

ID	user_email
1	email1@example.com
2	email2@example.com
3	email3@example.com
4	email4@example.com
5	email5@example.com

wp_learndash_user_activity (lua)

ID	user_id	post_id	activity_started	activity_complete
1	1	1	1668733909	1668733979
2	1	2	1668733909	1668733979
3	1	3	1668733909	1668733979
4	1	4	1668733909	1668733979
5	1	5	1668733909	1668733979
6	2	1	1668733909	1668733979
7	2	2	1668733909	1668733979
8	2	3	1668733909	1668733979
9	2	4	1668733909	1668733979
10	3	1	1668733909	1668733979
11	3	2	1668733909	1668733979

I have written this SQL

SELECT u.user_email AS user_email, p.post_title AS lesson_title, DATE_FORMAT(FROM_UNIXTIME(lua.activity_started), '%Y-%m-%d %H:%i:%s') AS activity_started, DATE_FORMAT(FROM_UNIXTIME(lua.activity_completed), '%Y-%m-%d %H:%i:%s') AS activity_completed
FROM wp_learndash_user_activity lua
JOIN wp_users u ON u.ID = lua.user_id
JOIN wp_posts p ON p.ID = lua.post_id
WHERE lua.post_id IN (1, 2, 3, 4, 5)
ORDER BY u.ID ASC

Which gives these results

user_email	lesson_title	activity_started	activity_completed
email1@example.com	Week 1	2022-11-18 01:11:49	2022-11-18 01:12:59
email1@example.com	Week 2	2022-11-18 01:11:49	2022-11-18 01:12:59
email1@example.com	Week 3	2022-11-18 01:11:49	2022-11-18 01:12:59
email1@example.com	Week 4	2022-11-18 01:11:49	2022-11-18 01:12:59
email1@example.com	Week 5	2022-11-18 01:11:49	2022-11-18 01:12:59
email2@example.com	Week 1	2022-11-18 01:11:49	2022-11-18 01:12:59
email2@example.com	Week 2	2022-11-18 01:11:49	2022-11-18 01:12:59
email2@example.com	Week 3	2022-11-18 01:11:49	2022-11-18 01:12:59
email2@example.com	Week 4	2022-11-18 01:11:49	2022-11-18 01:12:59
email3@example.com	Week 1	2022-11-18 01:11:49	2022-11-18 01:12:59
email3@example.com	Week 2	2022-11-18 01:11:49	2022-11-18 01:12:59

However, what I would really like is for it to show 5 rows for all users. That means if they did not have entries for the 5 posts (lua.post_id IN (1, 2, 3, 4, 5)) then they would still show the user_email and lesson_title but show NULL for activity_started and activity_complete

See the following for the desired results.

user_email	lesson_title	activity_started	activity_completed
email1@example.com	Week 1	2022-11-18 01:11:49	2022-11-18 01:12:59
email1@example.com	Week 2	2022-11-18 01:11:49	2022-11-18 01:12:59
email1@example.com	Week 3	2022-11-18 01:11:49	2022-11-18 01:12:59
email1@example.com	Week 4	2022-11-18 01:11:49	2022-11-18 01:12:59
email1@example.com	Week 5	2022-11-18 01:11:49	2022-11-18 01:12:59
email2@example.com	Week 1	2022-11-18 01:11:49	2022-11-18 01:12:59
email2@example.com	Week 2	2022-11-18 01:11:49	2022-11-18 01:12:59
email2@example.com	Week 3	2022-11-18 01:11:49	2022-11-18 01:12:59
email2@example.com	Week 4	NULL	NULL
email2@example.com	Week 5	NULL	NULL
email3@example.com	Week 1	2022-11-18 01:11:49	2022-11-18 01:12:59
email3@example.com	Week 2	2022-11-18 01:11:49	2022-11-18 01:12:59
email3@example.com	Week 3	NULL	NULL
email3@example.com	Week 4	NULL	NULL
email3@example.com	Week 5	NULL	NULL

Answer

To combine each user with each post use CROSS JOIN.
And then LEFT JOIN to matching lesson_user_activity

SELECT u.user_email AS user_email,
    p.post_title AS lesson_title,
    DATE_FORMAT(FROM_UNIXTIME(lua.activity_started), '%Y-%m-%d %H:%i:%s') AS activity_started,
    DATE_FORMAT(FROM_UNIXTIME(lua.activity_completed), '%Y-%m-%d %H:%i:%s') AS activity_completed
FROM wp_users u
    CROSS JOIN wp_posts p
    LEFT JOIN wp_learndash_user_activity lua
        ON u.ID = lua.user_id AND p.ID = lua.post_id
WHERE p.ID IN (1,2,3,4,5)
ORDER BY u.ID ASC, p.ID ASC

Advertisement

Answer