Simple Mysql table join problem

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?

From stackoverflow

• 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?

  Andrew Vit : what you're doing in your example is a cartesian product (all rows crossed against all other combinations of rows from other tables). You want to use a join on the id columns to reduce the data set before it hits the "where" clause.

  Bill Karwin : @Andrew: Most RDBMS brands know how to optimize those two cases identically. It's only a Cartesian product if you *don't* add conditions in the where clause.

  staticsan : It's easier to maintain if you use the JOIN syntax because it also separates the JOIN conditions from the WHERE conditions.

• 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)
  

• 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.

  James Socol : If you're trying to learn JOINs you could do this and then change "INNER" to different values ("LEFT", "RIGHT") to see how the result set changes.

• 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.

• 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'