...

View Full Version : List and number of from two tables



NeoPuma
03-08-2007, 12:35 AM
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

guelphdad
03-08-2007, 04:34 AM
what have you tried so far?

phoenixshade
03-08-2007, 06:48 AM
Try this SQL query:

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.

guelphdad
03-08-2007, 01:50 PM
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. :)

NeoPuma
03-08-2007, 03:25 PM
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 :p.
Just to let you know, my knowledge on MySQL is not 100% great, but I had tried the inner join.

phoenixshade
03-08-2007, 06:01 PM
Thanks Phoenixshade, and thanks quelphdad for trying. Homework question? I'd love homework like that :p.
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:

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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum