title: SQL query to get users that do have events in their calendar
body: <p>I have the following database tables:</p>
  
  <p>Users table:</p>
  
  <pre><code>| id  |  name |
  | 1   |  bob  |
  | 2   |  jane |
  | 3   |  tim  |
  | 4   |  rex  |
  </code></pre>
  
  <p>Events table:</p>
  
  <pre><code>| 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 |
  </code></pre>
  
  <p>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.</p>
  
  <p>Ideally this is what I would like to achieve:</p>
  
  <pre><code>| user_id | 12/05/2018 | 13/11/2018 | 14/11/2018 | 15/11/2018 |
  | 1       | 1          | 1          | 0          | 0          |
  | 2       | 0          | 0          | 0          | 1          |
  </code></pre>
  
  <p>So far I've got this below as a starting point, but it's not working as it returns zero rows:</p>
  
  <pre><code>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 )
  </code></pre>
  
  <p>Can anyone help me accomplish this? Is this even possible in 1 SQL query?</p>