CODEWITHSUNDEEP
mysqlsql
OctavianWR
OctavianWR

Reputation: 227

SQL join that not in grouped date

I have this user_activity table

user_id   date
1         2020-01-01 08:00:00
2         2020-01-01 08:05:00
1         2020-01-02 08:00:00

and user table

user_id
1
2
3

I want to know the user that don't have activity on all date. like this

date        user_id
2020-01-01  3
2020-01-02  2
2020-01-02  3

I'm trying this but can't figure it out how to grouped it with date

SELECT a.* FROM user_activity a
LEFT OUTER JOIN user u on a.user_id= u.user_id
where a.user_id is null

I already looking for the answer but there is no answer with grouped date

Upvotes: 0

Views: 35

Answers (1)

Gab
Gab

Reputation: 3520

SELECT u.user_id, all_dates.date
FROM user u
CROSS JOIN (SELECT DISTINCT DATE(date) as date FROM user_activity) as all_dates
WHERE NOT EXISTS (
  SELECT 1
  FROM user_activity ua
  WHERE ua.user_id = u.user_id
    AND DATE(ua.date) = all_dates.date
)

->

user_id     date
3           2020-01-01
3           2020-01-02
2           2020-01-02

Also created a fiddle for you to play with

Upvotes: 2

Related Questions