mySQL multiple INNER JOIN

Question

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

The three tables are:

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

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

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

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

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 nice result as expected:

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

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:

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 a result something like:

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

I expect we're just making some simple error. Any help would be greatly appreciated.

GolezTrol · Accepted Answer · 2010-12-21 19:24:45Z

4

You need something like this:

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

answered Dec 21, 2010 at 19:24

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Hartsock Over a year ago

@direct00. Note this could have some performance issues with large recordsets, due to the sub-query.

davek Over a year ago

+1 interesting: MySql doesn't always cope with COUNT DISTINCT very well; that's probably why this variation works better than the one I outlined.

davek · Accepted Answer · 2010-12-21 19:29:16Z

2

How about if you add a DISTINCT like this:

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

that way, for every group of businessDetails.name, you have the count of distinct clientDetails and records within that "window".

edited Dec 21, 2010 at 19:29

answered Dec 21, 2010 at 19:23

davek

23k11 gold badges80 silver badges97 bronze badges

4 Comments

GolezTrol Over a year ago

I think that will give you '1' for both counts. You could use distinct clientdetails.clientid and distinct records.recordid if both tables have their own unique ids.

davek Over a year ago

you're right! thanks for pointing out the oversight. have edited the answer accordingly.

direct00 Over a year ago

Correct - just adding DISTINCT returned 1 for both.

direct00 Over a year ago

Both tables have their own unique IDs, so I tried using DISTINCT with those instead, and the query timed out.

John Hartsock · Accepted Answer · 2010-12-21 19:25:10Z

1

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

answered Dec 21, 2010 at 19:25

John Hartsock

87.1k23 gold badges135 silver badges146 bronze badges

Collectives™ on Stack Overflow

mySQL multiple INNER JOIN

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related