title: How to get counts from two separate tables based on time?
body: <p>I have four tables, Companies, Vehicles, Quotes, and PhoneRequests.</p>
  
  <p>Companies may receive both Quotes and Requests on their Vehicles.</p>
  
  <p>Each Quote and Request is stamped with a time.</p>
  
  <p>I want to find the number of Quotes and Requests each company has received during each month as long as <em>either</em> the number of Quotes or Requests is greater than 0.</p>
  
  <p>I don't know how to group such that the month from two separate tables is the same.</p>
  
  <p>Here's what I've got so far:</p>
  
  <pre><code>SELECT
      company.name `company`,
      vehicle.name `vehicle`,
      DATE_FORMAT(COALESCE(quote.qtime, pr.date),'%Y-%m') `month`,
      COUNT(DISTINCT quote.qid) `quotes`,
      COUNT(DISTINCT pr.id) `phone_reqs`
  FROM companies company
      LEFT JOIN vehicles vehicle ON vehicle.compid=company.cid
      LEFT JOIN quote_vehicle qv ON qv.vehicle_id=vehicle.vid
      LEFT JOIN quotes quote ON qv.quote_id=quote.qid
      LEFT JOIN phone_requests pr ON pr.vehicle_id=vehicle.vid AND quote.qtime IS NULL OR DATE_FORMAT(quote.qtime,'%Y-%m')=DATE_FORMAT(pr.date,'%Y-%m')
  GROUP BY
      company.cid, `month`
  HAVING
      quotes &gt; 0 OR phone_reqs &gt; 0
  ORDER BY
      `month` DESC, `company` ASC
  </code></pre>
  
  <p>But it seems to be attributing the <code>phone_reqs</code> to the wrong company, probably because of that <code>AND quote.qtime IS NULL OR DATE_FORMAT(quote.qtime,'%Y-%m')=DATE_FORMAT(pr.date,'%Y-%m')</code> bit. I don't know how else to ensure the Quote and Request occurred during the same month though.</p>