Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: Count across two tables?
04-02-2009, 02:26 AM #1
- Join Date
- Jun 2002
- Thanked 0 Times in 0 Posts
Count across two tables?
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?
04-02-2009, 05:30 AM #2Code:
SELECT candidates.name, count(*) as number FROM mg, candidates WHERE mg.c_id = candidates.c_id GROUP BY candidates.name ORDER by number desc LIMIT 10