Inner Join MySQL duplicates

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Inner Join MySQL duplicates

Post 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. :|
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Inner Join MySQL duplicates

Post 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'
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Inner Join MySQL duplicates

Post 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 ;)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply