MySQL Joining Problems

Question

The following query returns strange results for me:

SELECT
    `Statistics`.`StatisticID`,
    COUNT(`Votes`.`StatisticID`) AS `Score`,
    COUNT(`Views`.`StatisticID`) AS `Views`,
    COUNT(`Comments`.`StatisticID`) AS `Comments`
FROM `Statistics`
LEFT JOIN `Votes` ON `Votes`.`StatisticID` = `Statistics`.`StatisticID`
LEFT JOIN `Views` ON `Views`.`StatisticID` = `Statistics`.`StatisticID`
LEFT JOIN `Comments` ON `Comments`.`StatisticID` = `Statistics`.`StatisticID`
GROUP BY `Statistics`.`StatisticID`
LIMIT 0, 10

I'm querying this on a table structure like the following:

(only data relevant to Statistics.StatisticID = 8)

Votes

StatisticID
    8

Views

StatisticID
    8
    8

Comments

StatisticID
    8
    8
    8
    8
    8

Now if I run this query I get the following result set:

StatisticID    Score    Views   Comments
     8           5        5        5

I knwo where the 5 comes from - the number of Comments - and this works if I take the comments statement out. Can anyone debug this as this is out of my reach (I'm relatively new with SQL).

Thanks, Ross

Answer 1

When joining like this, you will duplicate the data as many times as you find mathing rows in the other tables. This is fine, if you only have 1 corresponding row in each table.

Run this query without the grouping, and you will get an idea on why you get the same result on all counts. I would however have guessed that you'll get 10 as the count for every field (1*2*5) If you want to solve this, you need to call a subselect for every count.

SELECT
    s.`StatisticID`,
    (SELECT COUNT(*) FROM Votes WHERE Votes.StatisticID = s.StatisticID) AS Score,
    (SELECT COUNT(*) FROM Views WHERE Views.StatisticID = s.StatisticID) AS Views,
    (SELECT COUNT(*) FROM Comments WHERE Comments.StatisticID = s.StatisticID) AS Comments,
FROM `Statistics` s
LIMIT 0, 10

There are certain performance-issues with this if the outher result is big. You might optimize it a little by joining one of the tables however I'm uncertain if the queryparser would be smart enough to only run 1 single time for each grouped item. Hopefully it would. Otherwise you could split it into different queries.

Answer 2

Assuming you have an id field or similar on the votes/views/comments:

SELECT
    `Statistics`.`StatisticID`,
    COUNT(DISTINCT `Votes`.`VoteID`) AS `Score`,
    COUNT(DISTINCT `Views`.`ViewID`) AS `Views`,
    COUNT(DISTINCT `Comments`.`CommentID`) AS `Comments`
FROM `Statistics`
LEFT JOIN `Votes` ON `Votes`.`StatisticID` = `Statistics`.`StatisticID`
LEFT JOIN `Views` ON `Views`.`StatisticID` = `Statistics`.`StatisticID`
LEFT JOIN `Comments` ON `Comments`.`StatisticID` = `Statistics`.`StatisticID`
GROUP BY `Statistics`.`StatisticID`
LIMIT 0, 10

Not tested it, but think it should work. (We have to use the different field because the statisticID will always be the same within a given group ...)