title: MySQL optimize multiple subquery into nested inner join?
body: <p>I have 3 tables; contracts, dealers, and users.</p>
  
  <p>users have many dealers and dealers have many contracts but the contracts are not directly associated with the users.</p>
  
  <p>I am trying to build a report that gets me a monthly count of completed contracts grouped by user for the last 12 months.</p>
  
  <p>So far I have built a multiple subquery, which is very slow: <a href="http://sqlfiddle.com/#!2/d41a9/1/0" rel="nofollow">SQL Fiddle</a></p>
  
  <pre><code>SELECT *,
    ( SELECT count(*) FROM contracts
       WHERE
         dealer_id IN
           ( SELECT id FROM dealers WHERE user_id = User.id )
         AND status = 'Paid'
         AND completion_date BETWEEN
           '2012-08-01 00:00:00' AND '2012-08-31 23:59:59'
    ) AS Aug_2012,
  
    ( SELECT count(*) FROM contracts
       WHERE
         dealer_id IN
           ( SELECT id FROM dealers WHERE user_id = User.id )
         AND status = 'Paid'
         AND completion_date BETWEEN
           '2012-09-01 00:00:00' AND '2012-09-30 23:59:59'
    ) AS Sep_2012
  FROM users AS User
    WHERE
      id IN( SELECT user_id FROM dealers WHERE active = 1 AND user_id IS NOT NULL GROUP BY user_id )
      AND id != 1
    ORDER BY User.name ASC
  </code></pre>
  
  <p>Instead of the subquery which selects each month I'd like to use something like this:</p>
  
  <pre><code>COUNT(*) as last_12_months,
  SUM(case when MONTH(completion_date) = 8 then 1 else 0 end) as Aug_2012,
  SUM(case when MONTH(completion_date) = 9 then 1 else 0 end) as Sep_2012,
  etc.
  </code></pre>
  
  <p>Since I'd be returning multiple columns I would have to restructure it, but I'm not sure how. If I use an INNER JOIN what clause do I join on?</p>
  
  <h1>Here's the final query based on Mikhail's answer below:</h1>
  
  <pre><code>SELECT
  User.*,
  SUM(case when MONTH(completion_date) = 8 then 1 else 0 end) AS Aug_2012,
  SUM(case when MONTH(completion_date) = 9 then 1 else 0 end) AS Sep_2012,
  SUM(case when MONTH(completion_date) = 10 then 1 else 0 end) AS Oct_2012,
  SUM(case when MONTH(completion_date) = 11 then 1 else 0 end) AS Nov_2012,
  SUM(case when MONTH(completion_date) = 12 then 1 else 0 end) AS Dec_2012,
  SUM(case when MONTH(completion_date) = 1 then 1 else 0 end) AS Jan_2013,
  SUM(case when MONTH(completion_date) = 2 then 1 else 0 end) AS Feb_2013,
  SUM(case when MONTH(completion_date) = 3 then 1 else 0 end) AS Mar_2013,
  SUM(case when MONTH(completion_date) = 4 then 1 else 0 end) AS Apr_2013,
  SUM(case when MONTH(completion_date) = 5 then 1 else 0 end) AS May_2013,
  SUM(case when MONTH(completion_date) = 6 then 1 else 0 end) AS Jun_2013,
  SUM(case when MONTH(completion_date) = 7 then 1 else 0 end) AS Jul_2013,
  SUM(case when completion_date BETWEEN '2012-08-01 00:00:00' AND '2013-07-31 23:59:59' then 1 else 0 end) as last_12_months
  FROM users AS User
  LEFT OUTER JOIN 
  ( 
      SELECT id, user_id FROM dealers 
      WHERE active = 1 AND user_id IS NOT NULL
  ) AS Dealer ON User.id = Dealer.user_id
  LEFT OUTER JOIN
  (
      SELECT completion_date, status, dealer_id FROM contracts
      WHERE completion_date BETWEEN '2012-08-01 00:00:00' AND '2013-07-31 23:59:59' AND status = 'Paid' AND cancelled = 0
  ) AS Contract on Dealer.id = Contract.dealer_id
  WHERE
      User.id IN 
      (
          SELECT user_id FROM dealers
          WHERE active = 1 AND user_id IS NOT NULL
          GROUP BY user_id
      )
  GROUP BY
      User.id order by User.name asc
  </code></pre>
  
  <p>This is about 4 times faster.</p>