#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2005
    Posts
    418
    Rep Power
    20

    Selecting multiple usernames based on user ids


    Hi,
    Let's say I have the following two tables:
    user{ user_id, user_name }
    game{ game_id, player1, player2 }

    Example tables:
    user_id, username
    {1, Stefan1 }
    {2, Harry }
    {3, Pim }
    {4, Grumpy }

    game_id, player1, player2
    {1, 1, 2}
    {2, 2, 3}
    {3, 3, 4}

    Now I want to select all the games including the user_names of the players that participated in those games. So the result should be something like this:
    game_id, username1, username2
    {1, Stefan1, Harry}
    {2, Harry, Pim }
    {3, Pim, Grumpy}

    What I've got so far is this:
    Code:
    SELECT 	g.game_id,
    		g.player1, 
    		g.player2,
    		u.user_name,
    FROM 	game AS g, user AS u
    WHERE 	g.player1 = u.user_id
    However, this would only return the name of the first player. I really have no idea how to return the name of the second player.

    Hope you can help me out.

    Thanks in advance,
    Stefan1
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,378
    Rep Power
    391
    Code:
    select g.game_id,
           u1.user_name as player1,
           u2.user_name as player2
      from user as u1
      join game
        on u1.user_id = game.player1
      join user as u2
        on game.player2 = u2.user_id

    Comments on this post

    • Stefan1 agrees
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2005
    Posts
    418
    Rep Power
    20
    Works great, thanks!

IMN logo majestic logo threadwatch logo seochat tools logo