Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Unanswered: query JOIN question (2 JOINs ?)

    table prizes (l_playerid, s_playerid, l_prize, s_prize)
    1 - 1 - 500 - 350
    2 - 1 - 650 - 245
    1 - 2 - 500 - 65
    2 - 2 - 150 - 85
    3 - 3 - 95 - 125
    5 - 2 - 350 - 205

    table players (playerid, name)
    1 - Bob
    2 - Jay
    3 - Suzie
    4 - David
    5 - Rick
    6 - Jane

    WHERE prizes.l_playerid = players.playerid OR prizes.s_playerid = players.playerid


    HOW can I return this:

    result (l_player_name, s_player_name)
    Bob - Bob
    Jay - Bob
    Bob - Jay
    Jay - Jay
    Suzie - Suzie
    Rick - Jay


    With one query???

    Thanks,
    Rey

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    PHP Code:
    select lp.name as l_player_name
         
    sp.name as s_player_name
         
    l_prize
         
    s_prize
      from prizes
    inner       
      join players 
    as lp
        on prizes
    .l_playerid lp.playerid
    inner       
      join players 
    as sp
        on prizes
    .s_playerid sp.playerid 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •