Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to link 3 SQL tables and pass 0 value if the value is NULL

I am trying to create a query for my website to get the total number of active users for a selected time period and for a specific user account ID to filter for teams.

My current query works but without a specific user account ID so this is for the total users and for my weekly time period (active users the past week including today):

WITH RECURSIVE Dates AS (
    SELECT CURDATE() - INTERVAL 6 DAY AS date
    UNION ALL
    SELECT date + INTERVAL 1 DAY
    FROM Dates
    WHERE date < CURDATE()
) 
SELECT 
    CASE 
        WHEN "weekly" = "biannually" THEN DATE_SUB(date, INTERVAL (DAYOFWEEK(Dates.date) - 1) DAY) 
        WHEN "weekly" = "yearly" THEN DATE_SUB(date, INTERVAL (DAYOFMONTH(Dates.date) - 1) DAY) 
        WHEN "weekly" = "daily" THEN DATE_FORMAT(date, "%Y-%m-%d %H:00:00") 
    ELSE 
        date
    END AS id, 
COALESCE(COUNT(activity_user), 0) AS users
FROM Dates 
    LEFT JOIN activity ON date = activity_date
    LEFT JOIN users ON activity_user = users_id
GROUP BY id
ORDER BY id ASC;

This is the result:

+------------+-------+
| id         | users |
+------------+-------+
| 2024-10-02 |     25|
| 2024-10-03 |     64|
| 2024-10-04 |     10|
| 2024-10-05 |     22|
| 2024-10-06 |     27|
| 2024-10-07 |     16|
| 2024-10-08 |      3|
+------------+-------+

The issue is that if I try to do:

LEFT JOIN users ON activity_user = users_id WHERE users_account = 10 for my second left join, to filter for the account with ID of 10, I get an empty set instead of showing me the dates but with 0 users, for each date. The expected result should be:

+------------+-------+
| id         | users |
+------------+-------+
| 2024-10-02 |      0|
| 2024-10-03 |      0|
| 2024-10-04 |      0|
| 2024-10-05 |      0|
| 2024-10-06 |      0|
| 2024-10-07 |      0|
| 2024-10-08 |      0|
+------------+-------+

And if I do:

LEFT JOIN users ON activity_user = users_id AND users_account = 10, it gets disregarded completely and doesn’t work and I get my original result of:

+------------+-------+
| id         | users |
+------------+-------+
| 2024-10-02 |     25|
| 2024-10-03 |     64|
| 2024-10-04 |     10|
| 2024-10-05 |     22|
| 2024-10-06 |     27|
| 2024-10-07 |     16|
| 2024-10-08 |      3|
+------------+-------+

I have tried using every type of SQL JOIN for these tables but none of them seem to work. I simply want to filter by the user_account field and to show 0 on dates that have no active users. It works for the total amount of users but not when I want to filter for a specific team (user_account).

Tags:

2

Answers


  1. 0

    You outer join activities and users so as to get dates without activities. In an outer joined row, the user columns are null.

    With

    WHERE users_account = 10

    you dismiss all rows that have another user, but also all outer joined rows, because their user_account is null, thus dismissing any date from your result that doesn’t have a user-10 activity.

    With

    ON ... AND users_account = 10

    you properly outer join the users you are interested in, but then you COUNT(activity_user). This counts you all activities, no matter what user, as for other users you still joined the activity, but got empty user columns. So instead count the rows that have a user-10 match:

    COUNT(users_id)
    Login or Signup to reply.
  2. 0

    The below code is for where you got an empty result but expected all zeros. I think that meant user_account 10 did not log on those days.

    Can you try,

    WITH RECURSIVE Dates AS (SELECT CURDATE() - INTERVAL 6 DAY AS date
    UNION ALL
    SELECT date + INTERVAL 1 DAY
    FROM Dates
    WHERE date < CURDATE()) 
SELECT 
    CASE 
        WHEN "weekly" = "biannually" THEN DATE_SUB(date, INTERVAL (DAYOFWEEK(Dates.date) - 1) DAY) 
        WHEN "weekly" = "yearly" THEN DATE_SUB(date, INTERVAL (DAYOFMONTH(Dates.date) - 1) DAY) 
        WHEN "weekly" = "daily" THEN DATE_FORMAT(date, "%Y-%m-%d %H:00:00") 
    ELSE 
        date
    END AS id, 
if(users_account is null, "0", COALESCE(COUNT(activity_user), 0) ) AS users
FROM Dates 
    LEFT JOIN activity ON date = activity_date
    left JOIN users ON activity_user = users_id
where users_account = 10
GROUP BY id
ORDER BY id ASC;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search