-1

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
Bobby S
  • 4,006
  • 9
  • 42
  • 61
  • Does this answer your question? [How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?](https://stackoverflow.com/questions/3969452/how-to-select-from-two-tables-in-mysql-even-if-not-all-rows-in-one-table-have-co) – philipxy Nov 25 '22 at 04:05

1 Answers1

1

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
Chernyshev Ivan
  • 61
  • 4
  • That looks interesting but I think the one part it is missing is restricting either the p.ID or lua.post_id to the required input. Perhaps if I add WHERE p.ID IN (1, 2, 3, 4, 5) – Bobby S Nov 24 '22 at 09:34
  • 1
    @BobbyS My mistake. I have updated answer. lua.post_id IN (1,2,3,4,5) does not work, because it might be NULL. – Chernyshev Ivan Nov 24 '22 at 15:55