SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    Hong Kong
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    question about select statement

    I have two MySQL table like this:
    game_table:
    id|name|
    1|A|
    2|B|
    3|C|
    4|D|
    5|F|
    6|G|

    update_table:
    id|game_id|up_time
    1|3|41
    2|4|52
    3|3|51
    4|5|78
    5|6|38
    6|1|49

    Can I combine
    select name from game_table order by id desc
    and
    select count(id) from update_table where id = game_table.id and up_time > 50
    to get sth like...

    select game_table.name, update_table.count(id) from game_table, update_table where update_table.game_id=game_table.id and update_table.up_time > 50 order by game_table.id desc
    <= this is just my thought.... i believe there is no such query ..

    I would like to ask how can I write the query in order to meet my requirement ?

    Thanks all

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    select game_table.id
         , game_table.name
         , count(update_table.up_time) as over50count
      from game_table
    left outer
      join update_table
        on game_table.game_id
         = update_table.game_id
       and update_table.up_time > 50
    group
        by game_table.id
         , game_table.name   
    order
        by game_table.id desc
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •