MySQL multiple INNER JOIN

Question

We have three tables that we would like to query to find out the number of clients in the business and the number of records in the business.

Three tables:

businessDetails
-------------------
businessDetails.ID
businessDetails.name

clientDetails
-------------------
clientDetails.ID
clientDetails.businessDetailsID

records
-------------------
records.ID
records.businessDetailsID

We have no problems with the ability to select a count from two tables at a time (businessDetails plus or clientDetails OR records). for instance

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

This query gives us a good result, as expected:

--------------------------------
businessName    |   totalClients
--------------------------------
Initech         |   23
Cylon Inc       |   148
The Dude Ltd    |   71

The problem we are facing is how to make an invoice for both clients and records at a time. We tried the following query, but found that for some reason it multiplies the number of totalClients:

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

This returns the result:

--------------------------------------------------------
businessName    |   totalClients    |   totalRecords
--------------------------------------------------------
Initech         |   93              |    93
Cylon Inc       |   398             |    398
The Dude Ltd    |   215             |    215

I expect that we just make a simple mistake. Any help would be greatly appreciated.

Answer 1

- :

SELECT  
    businessDetails.name AS businessName,
    (SELECT count('x') FROM clientDetails WHERE clientDetails.businessDetailsID = businessDetails.businessDetailsID) as totalclients,
    COUNT(records.businessDetailsID) AS totalRecords
FROM 
    `businessDetails`
    INNER JOIN records ON records.businessDetailsID = businessDetails.ID
GROUP BY 
    businessDetails.name
ORDER BY
    totalClients DESC

Answer 2

DISTINCT :

SELECT  businessDetails.name AS businessName
        , COUNT(distinct clientDetails.ID) AS totalClients
        , COUNT(distinct records.ID) 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

, businessDetails.name "".

Answer 3

Count (Distinct [FieldName]) works in MYSQL. Give it a try.

SELECT  businessDetails.name AS businessName
        , COUNT(Distinct 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