Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    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?


  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Thanked 4,745 Times in 4,707 Posts
    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


    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts