[SOLVED] left join weirdness - mysql count functions returning wrong!

[theinfamousmielie]

Hey all,

 

Curious question. This is the query i'm running:

 

SELECT
Client.Name,
Count(Campaign.CampaignID) AS CampaignCount,
Count(Content.ContentID) AS ContentCount,
Max(Content.UploadDT) AS LastUpload
FROM (Client LEFT JOIN Content ON Client.ClientID = Content.ClientID) LEFT JOIN Campaign ON Client.ClientID = Campaign.ClientID
GROUP BY Client.Name

 

And this is the result i'm getting:

 

+-----------------------+---------------+--------------+---------------------+
| Name                  | CampaignCount | ContentCount | LastUpload          |
+-----------------------+---------------+--------------+---------------------+
| Digiworks             |             0 |            0 | NULL                |
| John Smith Industries |             0 |            0 | NULL                |
| Pfizer                |             6 |            6 | 2008-06-16 17:26:56 |
| Playback              |             0 |            0 | NULL                |
+-----------------------+---------------+--------------+---------------------+

 

 

... the problem is: there are only 3 content items, and 2 campaigns 3 * 2 = 6, but why it's returning both 6 for CampaignCount and ClientCount is beyond me. Any ideas? Is it a 'group by' problem?

[theinfamousmielie]

Nevermind, i couldn't find a way to get it right. The problem was multiple 'counts' of records and a third table involved ... so despite it's inefficiency for now i'm doing SELECT (SELECT COUNT(*) FROM ...)