Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2008
    Posts
    3

    Unanswered: complicated query help

    Hi, I need help with a complicated query.

    I have 2 tables in question, members and swim_times.

    The tables are joined together on members.member_id = swim_times.memberid but here's where it gets complicated:

    There are the following fields in the swim_times table

    eventid (int)
    pool_size (int)
    location (varchar)
    stroke (int)
    swim_time (varchar)

    Now, this table is for storing swimmers swim times. The eventid is from another table and holds the swim distances like 25m, 100m, 200m etc

    The pool_size is either 50 or 25

    The location is a free text field that the user can input when adding the swim time

    The stroke field is either 1, 2, 3, 4 or 5

    The swim_time is a varchar field storing the swim times like 00:47:71 (mm:ss:ms)

    I want to be able to select the swimmers best time (one result) for each of the variables like pool_size, stroke, eventid, location etc

    Any ideas?

    Here's the query that joins the tables

    select swim_times.*, members.*
    from members inner join swim_times on members.member_id = swim_times.memberid

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT pool_size, MIN(swim_time) 
      FROM swim_times 
     WHERE member_id = 42
    GROUP BY pool_size
    Code:
    SELECT stroke, MIN(swim_time) 
      FROM swim_times 
     WHERE member_id = 42
    GROUP BY stroke
    Code:
    SELECT location, MIN(swim_time) 
      FROM swim_times 
     WHERE member_id = 42
    GROUP BY location
    eck setterah
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The swim_time is a varchar field storing the swim times like 00:47:71 (mm:ss:ms)
    Ouch.
    Does your solution work through this correctly Rudy (gut reaction is "yes, it bodges its way through")?
    George
    Home | Blog

  4. #4
    Join Date
    Oct 2008
    Posts
    3
    georgev, are you asking me that question?

  5. #5
    Join Date
    Oct 2008
    Posts
    3
    @r937 I was looking for a single query, not 3 separate ones. The way it works is that it goes through the members table listing the members best swim times, but only their best per stroke, event, location etc.

    If a swim time has either the same stroke, location, event etc as another one, only the best (lowest) time is displayed.

    Does that make sense?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bpsintl
    If a swim time has either the same stroke, location, event etc as another one, only the best (lowest) time is displayed.
    Code:
    SELECT MIN(swim_time)
      FROM swim_times
     WHERE member_id = 42
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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