SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Apr 2001
    Location
    Clarks Summit, PA
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Query Difficulties

    I'm hoping there is an SQL guru out there who can help me here . . . For the purposes of this query, I have two tables:

    Table #1 - containts Events (name, description, etc.)
    Table #2 - contains dates and times

    Table #1 has a many-to-one relationship with Table #2 (any given event takes place on one or more dates).

    The challenge -- I would like to create a query that retrieves all Events (from Table #1) along with only the first related record in Table #2 (being the record with the earliest date).

    I have attempted queries using the DISTINCT keyword to no avail. This seems like it should be simple. Any ideas?

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select e.name, min(ed.date)
    from event e inner join event_date ed on e.id = ed.event_id
    group by e.id

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    jofa, most databases (mysql being a notorious and non-standard exception) will insist that the non-aggregate columns in the SELECT list also be in the GROUP BY

    enfer, if it is acceptable to return only an identifying column along with the min date, then jofa's query is fine, assuming you settle on either e.name or e.id for both SELECT and GROUP BY

    however, if other details from the event table are required, then you have to do it this way:
    Code:
    select e.id, e.name, e.description
         , ed.eventdate
      from events e
    inner 
      join eventdates ed
        on e.id = ed.event_id
     where ed.eventdate =
           ( select min(eventdate)
               from eventdates
              where event_id = e.id )
    since this involves a subquery, in mysql you have to do it in two steps -- see 3.5.4 The Rows Holding the Group-wise Maximum of a Certain Field
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by r937
    jofa, most databases (mysql being a notorious and non-standard exception) will insist that the non-aggregate columns in the SELECT list also be in the GROUP BY
    A little typo...
    Actually, I didn't know that such a query was allowed in MySql eek: or , I don't know)

  5. #5
    SitePoint Member
    Join Date
    Apr 2001
    Location
    Clarks Summit, PA
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937 and jofa - the subquery was what I was looking for. You assistance is appreciated!


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
  •