title: mySQL multiple INNER JOIN
body: <p>We have three tables that we would like to query to find out the count of clients per business and the number of records per business</p>
  
  <p>The three tables are:</p>
  
  <pre><code>businessDetails
  -------------------
  businessDetails.ID
  businessDetails.name
  
  clientDetails
  -------------------
  clientDetails.ID
  clientDetails.businessDetailsID
  
  records
  -------------------
  records.ID
  records.businessDetailsID
  </code></pre>
  
  <p>We don't have any problem with being able to select a count from two of the tables at one time (businessDetails plus either clientDetails OR records).  For example</p>
  
  <pre><code>SELECT  businessDetails.name AS businessName
          , COUNT(clientDetails.businessDetailsID) AS totalClients
  FROM `businessDetails`
  INNER JOIN clientDetails 
          ON clientDetails.businessDetailsID = businessDetails.businessDetailsID
  GROUP BY 
  businessDetails.name
  ORDER BY
  totalClients DESC
  </code></pre>
  
  <p>This query gives us a nice result as expected:</p>
  
  <pre><code>--------------------------------
  businessName    |   totalClients
  --------------------------------
  Initech         |   23
  Cylon Inc       |   148
  The Dude Ltd    |   71
  </code></pre>
  
  <p>The problem we are running into is how to do a count on both clientDetails AND records at once.  We tried the following query, but have found that it multiplies the number of totalClients for some reason:</p>
  
  <pre><code>SELECT  businessDetails.name AS businessName
          , COUNT(clientDetails.businessDetailsID) AS totalClients
          , COUNT(records.businessDetailsID) AS totalRecords
  FROM `businessDetails`
  INNER JOIN clientDetails 
          ON clientDetails.businessDetailsID = businessDetails.businessDetailsID
  INNER JOIN records ON records.businessDetailsID = businessDetails.ID
  GROUP BY 
  businessDetails.name
  ORDER BY
  totalClients DESC
  </code></pre>
  
  <p>This returns a result something like:</p>
  
  <pre><code>--------------------------------------------------------
  businessName    |   totalClients    |   totalRecords
  --------------------------------------------------------
  Initech         |   93              |    93
  Cylon Inc       |   398             |    398
  The Dude Ltd    |   215             |    215
  </code></pre>
  
  <p>I expect we're just making some simple error.  Any help would be greatly appreciated.</p>