Combining queries in MySQL

Question

Hey all,

I know you can combine multiple table-selects using a Join statement but is there a way to combine these two queries into one?

SELECT Statistics.StatisticID
FROM Statistics
ORDER BY `SubmittedTime` DESC
LIMIT 0, 10

And this one?

SELECT COUNT(Votes.StatisticID)
FROM Votes
WHERE Votes.StatisticID = ?

(fluff removed)

At the moment I've achieved something that nearly works.

SELECT Statistics.StatisticID, COUNT(Score.StatisticID) AS Votes
FROM `Statistics`
LEFT JOIN `Votes` AS `Score` ON `Statistics`.`StatisticID` = `Score`.`StatisticID`
ORDER BY `SubmittedTime` DESC
LIMIT 0, 10

The Votes table is a simple StatID, UserID joiner. In my test case it contains 3 rows, two with StatID 5 - 1 with StatID 2.

My query will work if I add a WHERE clause, for example WHERE StatisticID = 5 and correctly return 2 votes. However if I remove the WHERE clause I still get one row returned and 3 votes.

Is it possible to combine these queries or will I have to run the second for each result (obviously not preferable)?

Thanks, Ross

Answer 1

Assuming that you want to count the number of votes per statistics

SELECT StatisticID, COUNT(*) AS CountVotes
FROM `Votes`
GROUP BY Statistics.StatisticsID

I'm not seeing the reason why the tables have to be joined.

[EDIT] Ah...I see you want to order by submittedtime of the statistics table.

SELECT Votes.StatisticID, COUNT(*) AS CountVotes
FROM `Votes` JOIN Statistics ON votes.statisticsID = Statistics.StatisticsID
GROUP BY Statistics.StatisticsID
ORDER BY Statistics.SubmittedTime