title: Grouping by date and combining data from 3 tables
body: <p>The following are the three tables I have where session.id = signup.session_id AND session.loc_id = location.id. The max override is as the name suggest override the default max capacity for the location hence <code>IFNULL(session.max_override, location.max_cap)</code>.</p>
  
  <pre><code>mysql&gt; SELECT * FROM session;
  +----+---------------------+---------------+--------+
  | id | date_time           | max_override  | loc_id |
  +----+---------------------+---------------+--------+
  | 1  | 2014-02-04 10:30:00 |      35       |    2   | 
  | 2  | 2014-02-04 17:00:00 |               |    2   | 
  | 3  | 2014-02-06 11:30:00 |      50       |    2   | 
  | 4  | 2014-02-09 13:30:00 |               |    1   | 
  +----+---------------------+---------------+--------+
  
  mysql&gt; SELECT * FROM location;
  +-----------------+---------+
  | id | location   | max_cap |
  +-----------------+---------+
  |  1 | up         | 20      |
  |  2 | down       | 103     |
  |  3 | right      | 50      |
  |  4 | left       | 50      |
  +-----------------+---------+
  
  mysql&gt; SELECT * FROM signups;
  +-----------------+------------+
  | id | name       | session_id |
  +-----------------+------------+
  |  1 | test       |    3       |
  |  2 | admin      |    1       |
  |  3 | meme       |    2       |
  |  4 | anna       |    4       |
  +-----------------+------------+
  </code></pre>
  
  <p>The report I am trying to create looks simple but I am not sure how to approach the problem. The following is how I would like the report/output to look like..</p>
  
  <pre><code> mysql&gt; query ouput;
  +------------+----------+-----------+----------+----------+-----------+----------+
  | date       | am_time  | am_ses_id | am_spots | pm_time  | pm_ses_id | pm_spots |
  +------------+----------+-----------+----------+----------+-----------+----------+
  | 2014-02-04 | 10:30 AM |    1      |    34    | 05:00 PM |     2     |    102   |
  | 2014-02-06 | 11:30 AM |    3      |    49    |          |           |          |
  | 2014-02-09 |          |           |          | 01:30 PM |     4     |    49    |
  +------------+----------+-----------+----------+----------+-----------+----------+
  </code></pre>
  
  <p>I can group the date and time correctly and also managed to get the <code>session_id</code> to match since it is all within one table but to calculate the am/pm spots which is nothing but counting the records in signups table for a particular session and deducting the value from either the <code>max_cap</code> or <code>max_override</code> depending on the situation.</p>
  
  <p>THIS is what I tried</p>
  
  <p>Using the following query</p>
  
  <pre><code>SELECT 
      DATE_FORMAT(a.date_time,'%m/%d/%Y') AS ses_date,
      DATE_FORMAT(a.date_time,'%r') AS ses_time,
      a.id,
      COUNT(b.id) as signed_up,
      IFNULL(a.max_override,c.max_cap) AS cap
  FROM
      test.session a
      LEFT JOIN
          test.signups b
          ON (b.session_id = a.id)
      LEFT JOIN
          test.location c
          ON (c.id = a.loc_id)
  GROUP BY b.session_id
  </code></pre>
  
  <p>I get the following output</p>
  
  <pre><code>+------------+----------+--------+-----------+------+
  | date       | ses_time | ses_id | signed_up | cap  |
  +------------+----------+--------+-----------+------+
  | 2014-02-04 | 10:30 AM |    1   |    1      |  35  |
  | 2014-02-04 | 05:00 PM |    2   |    1      | 103  |
  | 2014-02-06 | 10:30 AM |    3   |    1      |  50  |
  | 2014-02-09 | 10:30 AM |    4   |    1      |  50  |
  +------------+----------+--------+-----------+------+
  </code></pre>
  
  <p>But I cannot seem to find a way to group it only by the date so the output would appear like desired! I don't know if I should union two queries either.</p>