title: SQL subqueries to try to get maximum difference of the same column in two tables
body: <p>I’m looking to get the max discrepancy between two tables per day, per id.  I have the following data in a mysql database</p>
  
  <pre><code>insert into test.foo values ('2010-01-10', 1, 10);
  insert into test.foo values ('2010-01-10', 1, 5);
  insert into test.foo values ('2010-01-10', 2, 10);
  insert into test.foo values ('2010-01-10', 2, 10);
  insert into test.foo values ('2010-01-10', 3, 15);
  insert into test.foo values ('2010-01-10', 3, 15);
  insert into test.foo values ('2010-01-11', 1, 5);
  insert into test.foo values ('2010-01-11', 1, 5);
  insert into test.foo values ('2010-01-11', 2, 5);
  insert into test.foo values ('2010-01-11', 2, 5);
  insert into test.foo values ('2010-01-11', 3, 5);
  insert into test.foo values ('2010-01-11', 3, 5);
  
  insert into test.bar values ('2010-01-10', 1, 5);
  insert into test.bar values ('2010-01-10', 1, 5);
  insert into test.bar values ('2010-01-10', 2, 5);
  insert into test.bar values ('2010-01-10', 2, 5);
  insert into test.bar values ('2010-01-10', 3, 5);
  insert into test.bar values ('2010-01-10', 3, 5);
  insert into test.bar values ('2010-01-11', 1, 10);
  insert into test.bar values ('2010-01-11', 1, 10);
  insert into test.bar values ('2010-01-11', 2, 5);
  insert into test.bar values ('2010-01-11', 2, 5);
  insert into test.bar values ('2010-01-11', 3, 5);
  insert into test.bar values ('2010-01-11', 3, 5);
  </code></pre>
  
  <p>Here is my query:</p>
  
  <pre><code>SELECT t1.`date`, t1.id, t1.sums, t2.sums, max(t1.sums - t2.sums) FROM
    (select `date`, id, sum(val) sums
     from test.foo
     group by `date`, id) as t1,
    (select `date`, id, sum(val) sums
     from test.bar
     group by `date`, id) as t2
  WHERE t1.`date` = t2.`date` AND t1.id = t2.id
  group by t1.`date`
  </code></pre>
  
  <p>I’m getting this result:</p>
  
  <pre><code>+---------------------+----+------+------+------------------------+
  | date                | id | sums | sums | max(t1.sums - t2.sums) |
  +---------------------+----+------+------+------------------------+
  | 2010-01-10 00:00:00 |  1 |   15 |   10 |                     20 |
  | 2010-01-11 00:00:00 |  1 |   10 |   20 |                      0 |
  +---------------------+----+------+------+------------------------+
  2 rows in set (0.00 sec)
  </code></pre>
  
  <p>I’d like to be getting this result:
  I’m getting this result:</p>
  
  <pre><code>+---------------------+----+------+------+------------------------+
  | date                | id | sums | sums | max(t1.sums - t2.sums) |
  +---------------------+----+------+------+------------------------+
  | 2010-01-10 00:00:00 |  1 |   15 |   10 |                     20 |
  | 2010-01-11 00:00:00 |  2 |   10 |   10 |                      0 |  &lt;----- 
  +---------------------+----+------+------+------------------------+
  </code></pre>
  
  <p>Can anyone help me?  I’d like to be getting the max difference, and then the line that went along with it.  This query gives me the correct difference, but not the id and sums that go with it.  A colleague suggested also grouping by id, but as I thought, that just flattened out the result and each id was listed instead of the one id for the day that had the max difference.</p>
  
  <p>Thanks much in advance</p>