MySQL connection problems

The following query gives me strange results:

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 am requesting this in a table structure, for example:

(only data related 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 set of results:

StatisticID    Score    Views   Comments
     8           5        5        5

      

I know where 5 of them are the number of comments - and it works if I take the comment utterance. Can anyone debug this as it is out of my reach (I'm relatively new to SQL).

Thanks Ross

+1


source to share


2 answers


Assuming you have an id field or similar on 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

      



Haven't tested it but thought it should work. (We have to use a different field because statIDID will always be the same within a given group ...)

+2


source


With a join like this, you will duplicate the data as many times as you find in other tables. It is okay if there is only one matching row in each table.

Run this query without grouping and you will get an idea of ​​why you get the same result across all counts. However, I would assume that you get 10 as the count for each field (1 * 2 * 5) If you want to solve this problem, you need to call a subquery for each account.



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 some performance issues if the output is large. You can optimize it a bit by joining one of the tables, but I'm not sure the queryparser will be smart enough to only run once for each grouped item. I hope so. Otherwise, you can split it into different queries.

+4


source







All Articles