Page 1 of 1

Inner Join MySQL duplicates

Posted: Thu Oct 23, 2008 4:40 pm
by JAB Creations
The following creates MySQL duplicates...

Code: Select all

SELECT `name`FROM game_categoryINNER JOIN games ON game_category.game_id = game_idWHERE game_category.category_id = '2'
The output looks like this...
Football Fanatics 2009
Angry Gopher Golfers
Destroy All Humans
Heroes of Might & Magic III
Diablo II
Football Fanatics 2009
Angry Gopher Golfers
Destroy All Humans
Heroes of Might & Magic III
Diablo II
Football Fanatics 2009
Angry Gopher Golfers
Destroy All Humans
Heroes of Might & Magic III
Diablo II
Football Fanatics 2009
Angry Gopher Golfers
Destroy All Humans
Heroes of Might & Magic III
Diablo II
Football Fanatics 2009
Angry Gopher Golfers
Destroy All Humans
Heroes of Might & Magic III
Diablo II
Football Fanatics 2009
Angry Gopher Golfers
Destroy All Humans
Heroes of Might & Magic III
Diablo II
...I'm not sure what I'm doing incorrectly. I'm executing this code straight through phpMyAdmin. I added describe at the beginning and the table and all I see are simple, all, and null for columns I haven't chosen? How can I improve my query so I only get the results once please? Changing the tag number (at the end of the query) seems to have an effect on how many times the results are duplicated though I'm not seeing any "AH-HA!" patterns. :|

Re: Inner Join MySQL duplicates

Posted: Thu Oct 23, 2008 5:00 pm
by JAB Creations
When in doubt I l love how I can use phpMyAdmin to visually display what I'd have to do extra programming for in PHP.

Before: SELECT `name` FROM game_category INNER JOIN games ON game_category.game_id = game_id WHERE game_category.category_id = '3'
_After: SELECT `name` FROM game_category INNER JOIN games ON game_category.game_id=games.id WHERE game_category.category_id='3'

Re: Inner Join MySQL duplicates

Posted: Thu Oct 23, 2008 6:05 pm
by VladSun
Post the data you have in these two tables.

And...
When you use join you should not use a field name alone - you should use it together with its table name.
[sql] SELECT    games.name.... [/sql]

Also, (well, may some argue) don't use game_id like names - id is enough ;)
And the last - you should choose just one naming way for your tables - plural or singular ;)