SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Jul 2007
    Location
    San Jose, California
    Posts
    355
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select Optimization

    I've come across this particular problem twice, and can imagine it happening in many places. Say you have two tables:
    player
    player_id int,
    name varchar;

    match
    match_id,
    player_1
    player_2
    score

    Now the issue is when selecting a match. I want to get a match so it says something like

    "Jon Hill" "Bill Anderson" 15-9

    The ways in the past that i've delt with this don't seem very efficient:

    Code SQL:
    SELECT (SELECT name FROM player WHERE player_id = player_1), (SELECT name FROM player WHERE player_id = player_1), score FROM MATCH

    Is there a better way to do this, so that I'm not doing two selects in every one?

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT
      match.score,
      t1.name AS `player1`,
      t2.name AS `player2`
    FROM match
    INNER JOIN player t1 ON t1.player_id = match.player_1
    INNER JOIN player t2 ON t2.player_id = match.player_2

  3. #3
    SitePoint Addict
    Join Date
    Jul 2007
    Location
    San Jose, California
    Posts
    355
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much, figured a join would be the answer but wasn't sure how to phrase it.


Bookmarks

Posting Permissions

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