SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: SQL question

Hybrid View

  1. #1
    SitePoint Addict richard_h's Avatar
    Join Date
    May 2002
    Location
    London
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL question

    I have the following table -
    ---------------
    + RESULTS +
    ---------------
    pk | user | round

    1 | 8 | 1
    2 | 8 | 2
    3 | 8 | 3
    4 | 5 | 1
    5 | 5 | 2
    6 | 5 | 1
    7 | 2 | 3

    How can I go about selecting all the user id's but only where that user has round 1,2 and 3.
    This means from the above example I would only have user - 8 returned. I'm using MySQL.

    Many thanks in advance.

  2. #2
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT user FROM results
    WHERE round = 1 
    AND round = 2
    AND round = 3;
    should work.

  3. #3
    SitePoint Addict richard_h's Avatar
    Join Date
    May 2002
    Location
    London
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your post Chris, unfortunately that'll return 0 results because the round field can only contain 1 value.

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,254
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Since MySQL doesn't have sub-queries(well the new version does, but it hasn't propogated yet), you can use something like this:

    Code:
    SELECT a.User 
       FROM results a, results b, results c
     WHERE a.User = b.User
    	 AND a.User = c.User
    	 AND a.round = 1
    	 AND b.round = 2
    	 AND c.round = 3
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  5. #5
    SitePoint Addict richard_h's Avatar
    Join Date
    May 2002
    Location
    London
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell
    Since MySQL doesn't have sub-queries(well the new version does, but it hasn't propogated yet), you can use something like this:

    Code:
    SELECT a.User 
       FROM results a, results b, results c
     WHERE a.User = b.User
    	 AND a.User = c.User
    	 AND a.round = 1
    	 AND b.round = 2
    	 AND c.round = 3
    That's what I need! many thanks.
    assuming the same user cannot have the same round more than once
    I'm afraid they can aswell as skip rounds, but thank-you for your comments.

    Am I correct in assuming Dave's select statement makes copies of the result table? - that's a nice trick!

  6. #6
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by richard_h
    Am I correct in assuming Dave's select statement makes copies of the result table? - that's a nice trick!
    Something like that, and also something not like that. It's basically a self-join where you join a table with itself. There is still only 1 instance of the "result" table.

    >> you are still not out of the woods yet, grasshopper

    That's a nice line

  7. #7
    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)
    assuming the same user cannot have the same round more than once -- and it must be an assumption, because where i would define the primary key as (user,round), you have a surrogate autonumber pk -- then you could simply do this:
    Code:
    select user
      from results
    group by user
    having count(*)=3
    rudy
    http://r937.com

  8. #8
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,254
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937
    assuming the same user cannot have the same round more than once -- and it must be an assumption, because where i would define the primary key as (user,round), you have a surrogate autonumber pk -- then you could simply do this:
    Code:
    select user
    from results
    group by user
    having count(*)=3
    rudy
    http://r937.com
    And assuming you can't skip a round for any reason also, this would be a better approach.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  9. #9
    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)
    yes, that would be worth checking too, to guard against 1,2,4, but if 1,2,3 are the only values, then count(*)=3 works

    ;-)

  10. #10
    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)
    you are still not out of the woods yet, grasshopper

    if a user can indeed have the same round more than once, then dave's three-way self-join will return multiple rows per round per user, so you need DISTINCT


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
  •