Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Oct 2004
    Posts
    104
    Thanks
    7
    Thanked 2 Times in 2 Posts

    List and number of from two tables

    Hi,
    I have two tables in my database:
    tbl_games
    tbl_cheats

    I want to get all the records from tbl_games, and for each game, get the number of records for it in tbl_cheats.

    In tbl_games the key field is game_id, and in the tbl_cheats table the field cheats_game_id would be the same (for that particular game - if that makes sense).

    How could I do this?

    Thanks in advance
    Matt

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    what have you tried so far?

  • #3
    Regular Coder
    Join Date
    Feb 2007
    Location
    near Washington, DC
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try this SQL query:
    Code:
    SELECT tbl_games.game_id, COUNT(*) as number_of_cheats
    FROM tbl_games
    INNER JOIN tbl_cheats
    ON tbl_games.game_id = tbl_cheats.cheats_game_id
    GROUP BY game_id
    This will return two columns: game_id, which is the unique key for each game, and number_of_cheats, which is the number of rows in tbl_cheats for that game. (If a game has no cheats, no row will be returned.)

    I'm fairly new to SQL, so let me know if it works.
    Last edited by phoenixshade; 03-08-2007 at 07:01 AM. Reason: Added table names to SELECT and ON
    — Wilford Nusser
    Validate Your Code: (X)HTML CSS
    An HTML Email is NOT a Web Page: HTML Email Guide (1.2Mb pdf) Webmail CSS Support
    REGEX: Brought to you by Psychotic Crack-Smoking Monkeys

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    phoenixshade, the reason I asked the original poster what they have tried, rather than provide a solution, is because the question is rather straightforward. It looks like a homework question, which are not allowed to be posted without noting such.

    That type of question has also been asked and answered several times on this and other forums.

    Finding out what the o.p. has tried can give you an idea if they merely need a nudge to the right answer or an explanation on how to get there.

    Just my two cents as a moderator.

  • #5
    Regular Coder
    Join Date
    Oct 2004
    Posts
    104
    Thanks
    7
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by guelphdad View Post
    phoenixshade, the reason I asked the original poster what they have tried, rather than provide a solution, is because the question is rather straightforward. It looks like a homework question, which are not allowed to be posted without noting such.

    That type of question has also been asked and answered several times on this and other forums.

    Finding out what the o.p. has tried can give you an idea if they merely need a nudge to the right answer or an explanation on how to get there.

    Just my two cents as a moderator.
    Thanks Phoenixshade, and thanks quelphdad for trying. Homework question? I'd love homework like that .
    Just to let you know, my knowledge on MySQL is not 100% great, but I had tried the inner join.

  • #6
    Regular Coder
    Join Date
    Feb 2007
    Location
    near Washington, DC
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by NeoPuma View Post
    Thanks Phoenixshade, and thanks quelphdad for trying. Homework question? I'd love homework like that .
    Just to let you know, my knowledge on MySQL is not 100% great, but I had tried the inner join.
    The COUNT(*) and GROUP BY are the keys. I just realized that you don't even need the inner join. You could just do this:
    Code:
    SELECT cheats_game_id, COUNT(*)
    FROM tbl_cheats
    GROUP BY cheats_game_id
    Unless you need the title of the game in the output, you don't need to reference tbl_games at all.

    guelph: The question as asked didn't smack of a homework question to me. NeoPuma seemed to boil the question down to its essential elements and asked a very concise question. Most of the "homework trollers" post very vague questions and end with somthing like "how do u do that HEELLLPP!!!11!!" Just my two cents as a user, but I'll try to exercise a little more caution in future.
    Last edited by phoenixshade; 03-08-2007 at 06:05 PM.
    — Wilford Nusser
    Validate Your Code: (X)HTML CSS
    An HTML Email is NOT a Web Page: HTML Email Guide (1.2Mb pdf) Webmail CSS Support
    REGEX: Brought to you by Psychotic Crack-Smoking Monkeys


  •  

    Posting Permissions

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