Combining queries in MySQL

Question

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)?

Vincent Ramdhanie · Accepted Answer · 2008-11-18 21:14:03Z

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

edited Nov 18, 2008 at 21:14

answered Nov 18, 2008 at 21:06

Vincent Ramdhanie

103k23 gold badges143 silver badges196 bronze badges

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

Collectives™ on Stack Overflow

Combining queries in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related