SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict Smolf3d's Avatar
    Join Date
    Aug 2004
    Location
    russia
    Posts
    224
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question Need some help with query

    This is work fine

    Code SQL:
    SELECT contests_statistic.START
         , contests_statistic.END
         , contests_statistic.FIRST
         , contests_statistic.SECOND
         , contests_statistic.third
         , contests_statistic.prise_first
         , contests_statistic.prise_second
         , contests_statistic.prise_third
         , contests_statistic.firstsmscontests
         , contests_statistic.secondsmscontests
         , contests_statistic.thirdsmscontests
         , users.id AS firstid
         , users.country AS firstcountry
         , ( SELECT id
         FROM users
         WHERE username = contests_statistic.SECOND) AS secondid
         , ( SELECT country
         FROM users
         WHERE username = contests_statistic.SECOND) AS secondcountry
         , ( SELECT id
         FROM users
         WHERE username = contests_statistic.third) AS thirdid
         , ( SELECT country
         FROM users
         WHERE username = contests_statistic.third) AS thirdcountry
         FROM contests_statistic 
         LEFT JOIN users 
         ON contests_statistic.FIRST = users.username
         ORDER BY contests_statistic.id DESC LIMIT 2

    But how can i optimization this query?
    Last edited by Smolf3d; Mar 29, 2008 at 13:24.

  2. #2
    Trash Boat mkoenig's Avatar
    Join Date
    Aug 2007
    Posts
    1,232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats over my head.

    You are in Israel? Im Jewish god bless.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT contests_statistic.start
         , contests_statistic.end
         , contests_statistic.first
         , contests_statistic.second
         , contests_statistic.third
         , contests_statistic.prise_first
         , contests_statistic.prise_second
         , contests_statistic.prise_third
         , contests_statistic.firstsmscontests
         , contests_statistic.secondsmscontests
         , contests_statistic.thirdsmscontests
         , user1.id      AS firstid
         , user1.country AS firstcountry
         , user2.id      AS secondid
         , user2.country AS secondcountry
         , user3.id      AS thirdid
         , user3.country AS thirdcountry
      FROM contests_statistic 
    LEFT OUTER
      JOIN users AS user1
        ON user1.username = contests_statistic.first
    LEFT OUTER
      JOIN users AS user2
        ON user2.username = contests_statistic.second
    LEFT OUTER
      JOIN users AS user3
        ON user3.username = contests_statistic.third
    ORDER 
        BY contests_statistic.id DESC LIMIT 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict Smolf3d's Avatar
    Join Date
    Aug 2004
    Location
    russia
    Posts
    224
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thanks r937


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
  •