Web Analytics Made Easy -
StatCounter Count across two tables? - CodingForum


No announcement yet.

Count across two tables?

  • Filter
  • Time
  • Show
Clear All
new 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?


  • #2
    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
    Be yourself. No one else is as qualified.