View Full Version : Count across two tables?

04-02-2009, 02:26 AM
i have a table mg with a column c_id. C_id means a vote for a candidate in table candidates. C_id points to the id field in table candidates. So the following query nicely shows the top ten vote getters. However, naturally it lists them by an integer number, since c_id is a pointer into table candidates. So I would like to display the candidate name field which is stored in table candidates And I have tried many joins and all manner of queries with no success. So here is the query from table mg only -- this works fine.

SELECT c_id, COUNT(*) as number FROM mg GROUP BY c_id ORDER by number desc LIMIT 10

Here is an example of a query using both tables that does not work:

SELECT candidates.name count(*) as number From mg, candidates
GROUP BY mg.c_id ORDER by number desc LIMIT 10

How can I show the list of top vote getters?


Old Pedant
04-02-2009, 05:30 AM
SELECT candidates.name, count(*) as number
FROM mg, candidates
WHERE mg.c_id = candidates.c_id
GROUP BY candidates.name
ORDER by number desc