title: sql query to find all the sessions of the events with number of attendees
body: <p>I'm trying to get all the sessions of an event and then count of all the attendees who have subscribed to that event (session_attendee table).</p>
  
  <p><strong>My Schema</strong></p>
  
  <p>5 Tables</p>
  
  <p><em>Event -> day -> session</em></p>
  
  <p><em>attendee</em></p>
  
  <p><em>session_attendee</em></p>
  
  <pre><code>+------------------------------+
  |           attendee           |
  +------------------------------+
  | id  username    password     |
  | 1   user1       test         |
  | 2   user2       test         |
  | ------                       |
  | event                        |
  | id  name                     |
  | 1   event 1                  |
  | 2   event 2                  |
  | 3   event 3                  |
  | -----                        |
  | day                          |
  | id  date            event_id |
  | 1   '2015-06-01'    1        |
  | 2   '2015-06-02'    1        |
  | 3   '2015-07-01'    2        |
  | 4   '2015-07-02'    2        |
  | ------                       |
  | session                      |
  | id  name        day_id       |
  | 1   session a   1            |
  | 2   session b   1            |
  | 3   session c   2            |
  | 4   session d   2            |
  | ------                       |
  | session_attendee             |
  | id  session_id  attendee_id  |
  | 1   1           1            |
  | 2   2           1            |
  | 3   1           2            |
  | 4   2           2            |
  +------------------------------+
  </code></pre>
  
  <p><strong>My Expectation</strong></p>
  
  <pre><code>+--------------+-----------+
  | Session Name | Attendees |
  +--------------+-----------+
  | session a    |         2 |
  | session b    |         2 |
  | session c    |         0 |
  | session d    |         0 |
  +--------------+-----------+
  </code></pre>
  
  <p><strong>My Attempt</strong> (it might have some extra columns)</p>
  
  <pre><code>SELECT day.event_id, session.id, session.name,
  (SELECT COUNT(*) FROM day WHERE day.event_id = event.id) AS days,
  (SELECT COUNT(distinct attendee_id)) AS attendees
  FROM event
  LEFT JOIN day ON event.id = day.event_id
  LEFT JOIN session ON day.id = session.day_id
  LEFT JOIN session_attendee ON session.id = session_attendee.session_id
  WHERE day.event_id = 1
  ORDER BY day.date, session.start;
  </code></pre>
  
  <p><strong>SQL Fiddle</strong></p>
  
  <p><a href="http://sqlfiddle.com/#!9/72ffc" rel="nofollow">http://sqlfiddle.com/#!9/72ffc</a></p>
  
  <p>Edit</p>
  
  <p>Guys, please check this out, it works, any comments please.</p>
  
  <pre><code>SELECT event.id, day.date, session.name, session.day_id, session_attendee.id, session_attendee.session_id, session_attendee.attendee_id,
  COUNT(distinct attendee_id) AS attendees,
  (SELECT COUNT(*) FROM day WHERE day.event_id = 1) AS days
  FROM session
  RIGHT JOIN session_attendee ON session.id = session_attendee.session_id 
  RIGHT JOIN day ON session.day_id = day.id
  RIGHT JOIN event ON day.event_id = event.id
  WHERE event_id = 1
  GROUP BY session.name
  ORDER BY day.date;
  </code></pre>