Simple Mysql table join problem

Question

I'm designing a website to teach myself how to use table joins in mysql and have got stuck pretty early on. (The numbers are Wii friend codes, if that helps you make sense of this!)

I have 3 tables:

users:

id, firstname, surname

games:

id, title

numbers:

number, users_id, game_id

The number is a unique code that belongs to the user and associates with a game.

I'm trying to get out a list of numbers for a specific game along with the names of the users they belong to.

I'm running the query:

SELECT firstname, surname, number FROM games, users, numbers WHERE numbers.game_id = games.id AND games.title = 'foogame'

for instance, to get out all the numbers belonging to foogame. However, this returns any user who doesn't have a code for this game with a random code from another user. How do I make this query only return the users who actually have a code for that game?

Answer 1

Ah - just writing the question helped me!

SELECT firstname, surname, number FROM games, users, numbers WHERE numbers.game_id = games.id AND games.title = 'foogame' AND games.user_id = users.id

Simple!

Any ideas for optimising this?

Answer 2

SELECT firstname, surname, number
FROM games g, numbers n, users u
WHERE g.title = 'foogame'
  AND n.game_id = g.id
  AND u.id = n.users_id

To optimize, create indexes:

CREATE INDEX ix_game_title ON games (title)

CREATE INDEX ix_numbers_game ON numbers (game_id, users_id)

and, of course, make your games.id and users.id primary keys.

If you'll ever want to search for numbers in all games given a certain user, you'll also need:

CREATE INDEX ix_numbers_users ON numbers (users_id)

Answer 3

I find it easier if you actually use joins because you won't miss a join condition like you did the first time.

SELECT firstname, surname, number 
FROM users u
INNER JOIN numbers n
  on n.users_id = u.id
INNER JOIN games g
  on n.game_id = g.id
WHERE g.title = 'foogame'

INNER JOIN is the equivalent of what you did - it limits specifically.

Answer 4

You need to link the numbers table's users_id and game_id columns to the users and games id cols. Just add:

AND users_id = users.id

AND game_id = games.id

You could also use a JOIN instead.

Answer 5

To follow up on Tom's answer... If you are new to MySQL, I'd suggest breaking it down in steps and be very clear on what is being grabbed from where. You can treat it as a join of two tables in which the 3rd then hops in. You can also use brackets which allow some separation when you come back to it a few months later.

SELECT firstname, surname, number 
    FROM 
    (
        (users u INNER JOIN numbers n ON(n.user_id = u.id) )
        INNER JOIN games g ON(n.game_id = g.id)
    )
    WHERE g.title = 'foogame'