title: Compare number of rows in different tables group by date
body: <p>I would like to connect MySQL query to a PHP or JSON line chart (Number of records per Date) with multiple lines (Topic1, Topic2).</p>
  
  <p>Example of Topic1 MySQL table:</p>
  
  <pre><code> created_at           |  text    
  ------------------------------------------------
   2015-07-21 10:10:40  |  hello this is a text
   2015-07-23 06:12:45  |  text number 2
   2015-07-23 10:12:45  |  text number 3
   2015-07-24 10:17:50  |  blah blah
  </code></pre>
  
  <p>Example of Topic2 MySQL table:</p>
  
  <pre><code> created_at           |  text    
  -----------------------------------------------------
   2015-07-21 10:13:23  |  hello this is another text
   2015-07-22 06:12:25  |  text number 4
   2015-07-23 10:13:09  |  text number 5
   2015-07-24 12:58:27  |  blah blah blah
  </code></pre>
  
  <p>So I was thinking to create a new table to display the chart.</p>
  
  <p>Expected output of new table (table_count) for chart:</p>
  
  <pre><code> Date        |  Topic1    | Topic2    
  -----------------------------------------------------
   2015-07-21  |    1       |    1
   2015-07-22  |   NULL     |    1
   2015-07-23  |    2       |    1
   2015-07-24  |    1       |    1
  </code></pre>
  
  <p>I have tried this MySQL query </p>
  
  <pre><code>INSERT INTO table_count (Date, Topic1)
  SELECT date_format(created_at,'%y-%m-%d') AS Date , COUNT(*)  
  FROM Topic1
  GROUP BY Date;
  </code></pre>
  
  <p>and </p>
  
  <pre><code>INSERT INTO table_count (Date, Topic2)
  SELECT date_format(created_at,'%y-%m-%d') AS Date , COUNT(*)  
  FROM Topic2
  GROUP BY Date;
  </code></pre>
  
  <p>But the output would be </p>
  
  <pre><code> Date        |  Topic1    | Topic2    
  -----------------------------------------------------
   2015-07-21  |    1       |   NULL
   2015-07-23  |    2       |   NULL
   2015-07-24  |    1       |   NULL
   2015-07-21  |   NULL     |    1
   2015-07-22  |   NULL     |    1
   2015-07-23  |   NULL     |    1
   2015-07-24  |   NULL     |    1
  </code></pre>
  
  <p>Thank you in advance!</p>
  
  <p><strong>Edit (Solved):</strong> </p>
  
  <pre><code>SELECT a.Date, IFNULL(a.Count,'0') as Topic1, IFNULL(b.Count,'0') as Topic2
  FROM
  (SELECT date_format(created_at, '%y-%m-%d') as Date, COUNT(*) as Count FROM Topic1 GROUP BY Date) a
  LEFT JOIN
  (SELECT date_format(created_at, '%y-%m-%d') as Date, COUNT(*) as Count FROM Topic2 GROUP BY Date) b
  ON a.Date = b.Date;
  </code></pre>