Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2010
    Posts
    53

    Unanswered: SQL query to compare data in different rows

    I had this question at a job interview yesterday and although it seemed pretty straight fwd I counldn't figure it out and it's kept me awake all night thinking about it.

    The system records data about a scrabble league there is a members table, a games table and a joining member_games table.

    members: member_id, name : pk(member_id)
    member_games: game_id, member_id, score : pk(game_id, member_id)
    games: game_id, location, date : pk(game_id)

    members
    1, mick
    2, keith
    3, charlie

    member_games
    1, 1, 50
    1, 2, 60
    2, 1, 45
    2, 3, 105
    3, 1, 30
    3, 3, 120

    game
    1, london, 2012-12-01
    2, richmond, 2012-12-02
    3, leeds, 2012-12-03

    How do you formulate an sql query to find out the number of wins for the member_id = 1?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some ideas.
    (Not tested on MySQL.)

    Example 1: Get the number of wins for the member_id = 1.
    Code:
    SELECT COUNT( CASE score_mem
                  WHEN score_win THEN
                       0
                  END
                ) AS number_of_wins
     FROM (SELECT game_id
                , MAX( CASE mg.member_id
                       WHEN 1 THEN
                            score
                       END
                     )         AS score_mem
                , MAX( score ) AS score_win
            FROM  member_games AS mg
            GROUP BY
                  game_id
          ) gm
    ;
    Example 2: Get the name and the number of wins for the member_id = 1.
    Code:
    SELECT mb.name
         , COUNT( CASE score_mem
                  WHEN score_win THEN
                       0
                  END
                )  AS number_of_wins
     FROM  members AS mb
     CROSS JOIN
          (SELECT game_id
                , MAX( CASE mg.member_id
                       WHEN mb.member_id THEN
                            score
                       END ) AS score_mem
                , MAX(score) AS score_win
            FROM  member_games AS mg
            GROUP BY
                  game_id
          )
     WHERE mb.member_id = 1
     GROUP BY
           mb.name
    ;
    Example 3: Get the member_id, the name and the number of wins for all members.
    Code:
    SELECT mb.member_id
         , MAX(mb.name) AS name
         , COUNT( CASE gm.score_mem
                  WHEN gm.score_win THEN
                       0
                  END
                )  AS number_of_wins
     FROM  members AS mb
     CROSS JOIN
          (SELECT game_id
                , MAX( CASE mg.member_id
                       WHEN mb.member_id THEN
                            score
                       END ) AS score_mem
                , MAX(score) AS score_win
            FROM  member_games AS mg
            GROUP BY
                  game_id
          ) gm
     GROUP BY
           mb.member_id
    ;
    .
    Last edited by tonkuma; 02-09-13 at 11:03.

  3. #3
    Join Date
    Apr 2010
    Posts
    53
    Many thanks for those...

Tags for this Thread

Posting Permissions

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