SQL query to get users that do have events in their calendar

Question

I have the following database tables:

Users table:

| id  |  name |
| 1   |  bob  |
| 2   |  jane |
| 3   |  tim  |
| 4   |  rex  |

Events table:

| id  | user_id | date       |
| 1   |    1    | 12/05/2018 |
| 2   |    1    | 13/05/2018 |
| 2   |    2    | 15/05/2018 |
| 3   |    2    | 16/05/2018 |
| 4   |    3    | 27/05/2018 |

I would like to construct a SQL query that retrieves all users who DO have any events within the upcoming 7 days AND also returns a column for each of the 7 days with a count of how many events that user has booked for each of those days.

Ideally this is what I would like to achieve:

| user_id | 12/05/2018 | 13/11/2018 | 14/11/2018 | 15/11/2018 |
| 1       | 1          | 1          | 0          | 0          |
| 2       | 0          | 0          | 0          | 1          |

So far I've got this below as a starting point, but it's not working as it returns zero rows:

SELECT
    users.id,
    name
FROM
    `users`
    inner join bookings on `users`.`id` = bookings.teacher_id
WHERE
    bookings.`date` BETWEEN NOW() AND DATE_ADD( NOW(), INTERVAL 7 DAY )

Can anyone help me accomplish this? Is this even possible in 1 SQL query?

Your question contradicts itself, you wrote: "users who do NOT have any events within the upcoming 7 days AND also returns a column for each of the 7 days with a count of how many events that user has booked for each of those days." - if a user does not have any events then the counts will all be zero. Please rephrase your question. — Dai, Oct 31 '18 at 02:59
Actually here you need LEFT JOIN booking on user.id = booking.teacher_id AND bookings.date BETWEEN NOW() AND DATE_ADD( NOW(), INTERVAL 7 DAY ) WHERE ISNULL booking.teacher_id ## This si because you want element from table users that have no link to booking — Antony Gibbs, Oct 31 '18 at 03:31
Consider handling issues of data display in application code — Strawberry, Oct 31 '18 at 07:21
Sorry, you're right, I mean where users DO have events, I've updated the question. — ajgisme, Oct 31 '18 at 09:11
Follow what Strawberry has suggested. RDBMS (SQL) should not generally be used for generating dynamic number of columns as output — Madhur Bhaiya, Oct 31 '18 at 09:31

Antony Gibbs · Answer 1 · 2018-10-31T06:42:21.230

0

Actually here you need LEFT JOIN
This si because you want elements from table 'user' that have no links in table booking.
Using LEFT JOIN will return all elements in table user, wether it has a booking or not, then using a WHERE condition you filter and keep only those who do not have a booking (booking.id IS NULL)

SELECT
    u.id AS userId,
    u.name AS userName
FROM
    `user` AS u
    LEFT JOIN booking AS b ON (
            u.id = b.userId
            AND b.`date` BETWEEN NOW() AND DATE_ADD( NOW(), INTERVAL 7 DAY )
        )
WHERE
    ISNULL(b.id)

in sql convention is to have table name in singular ... in nosql it's the other way, just to confuse everything...

edited Oct 31 '18 at 06:42

answered Oct 31 '18 at 03:42

Antony Gibbs

1,321
14
24

1

For the benefit of future people who may search for this answer, providing some details on why this solution works would be helpful. – KevinO Oct 31 '18 at 04:10
There's no convention – Strawberry Oct 31 '18 at 07:20
There might not be a defined convention, but in practice it's fare more convenient to use singular - https://stackoverflow.com/questions/338156/table-naming-dilemma-singular-vs-plural-names – Antony Gibbs Oct 31 '18 at 15:10

score 0 · Accepted Answer · answered Oct 31 '18 at 06:33

It is very hard to generate column names as strings of next dates (like '2018-11-01', '2018-11-02') without hardcoding.

But I could come with an answer which column names are named as d1..d7. Hope it is fine in your case. This might not be the optimal solution, but it works.

SELECT
  tt.user_id,
  SUM(tt.d1) AS d1,
  SUM(tt.d2) AS d2,
  SUM(tt.d3) AS d3,
  SUM(tt.d4) AS d4,
  SUM(tt.d5) AS d5,
  SUM(tt.d6) AS d6,
  SUM(tt.d7) AS d7
FROM
  (SELECT
   t.user_id,
   (CASE WHEN t.`date` = DATE(DATE_ADD(NOW(), INTERVAL 1 DAY)) THEN t.total ELSE 0 
END) AS d1,
   (CASE WHEN t.`date` = DATE(DATE_ADD(NOW(), INTERVAL 2 DAY)) THEN t.total ELSE 0 
END) AS d2,
   (CASE WHEN t.`date` = DATE(DATE_ADD(NOW(), INTERVAL 3 DAY)) THEN t.total ELSE 0 
END) AS d3,
   (CASE WHEN t.`date` = DATE(DATE_ADD(NOW(), INTERVAL 4 DAY)) THEN t.total ELSE 0 
END) AS d4,
   (CASE WHEN t.`date` = DATE(DATE_ADD(NOW(), INTERVAL 5 DAY)) THEN t.total ELSE 0 
END) AS d5,
   (CASE WHEN t.`date` = DATE(DATE_ADD(NOW(), INTERVAL 6 DAY)) THEN t.total ELSE 0 
END) AS d6,
   (CASE WHEN t.`date` = DATE(DATE_ADD(NOW(), INTERVAL 7 DAY)) THEN t.total ELSE 0 
END) AS d7

  FROM
  (
    SELECT b.user_id, b.`date`, COUNT(*) as total 
    FROM `bookings` b
    WHERE b.`date` BETWEEN NOW() AND DATE_ADD( NOW(), INTERVAL 7 DAY )
    GROUP BY b.user_id, b.`date`) t
  ) tt

 GROUP BY tt.user_id

This query gives me a result like this for a table like you given.

| user_id | d1 | d2 | d3 | d4 | d5 | d6 | d7 |
----------------------------------------------
|    1    | 1  | 0  | 0  | 0  | 0  | 0  | 0  |
|    2    | 0  | 1  | 1  | 0  | 0  | 0  | 0  |
|    3    | 0  | 0  | 0  | 0  | 1  | 0  | 0  |

Here d1 means, the next day. d2 means day after tomorrow, etc.

Also this solution gives column with all zeros as well (i.e. days without any events)

SQL query to get users that do have events in their calendar

2 Answers2