SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL - using min and max to retrive rows

    im doing a website for a univeristy that holds course information

    i have a table - courses - which hold information about courses on offer (not their dates)

    now the same course can be run or offered many times during a year

    so i have a table - courseoffering - which holds the location the course will be run at, its price etc

    now, these courseofferings can run for multiple days or dates (and the university wants to store each day, not just the start day and the end day because each day might start on a different time etc)

    so i have a table - courseofferingdates - which will hold a list of days the courseoffering will run for

    i need to get a list of all the courseofferings, for a specified course, and the date the courseoffering starts, and the day it ends

    so i want:

    Code:
    SELECT
    	courseoffering.courseoffering_id,// return all the offerings the course has
    	min(courseofferingdates.date), // plus the min and max date for each of the offerings
    	max(courseofferingdates.date)
    FROM
    	courseofferingdates, courseoffering
    WHERE
    	courseoffering.courseoffering_id = courseofferingdates.courseoffering_id
    AND
    	courseoffering.course_id = #mycourse_id# // for the course the user selects

  2. #2
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ???
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  3. #3
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what needs a better explanation?

  4. #4
    SitePoint Enthusiast
    Join Date
    Jan 2003
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Imminent
    what needs a better explanation?
    Well, for starters, you haven't mentioned which database system you are using or even what error happens when you try the query you posted above.

    Or, if the query you posted does run, then what about it doesn't do what you want?

  5. #5
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats not a real query. its pseudo sql/code

    sorry if that was the source of confusion

    DB: access

  6. #6
    SitePoint Enthusiast
    Join Date
    Jan 2003
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe it's pseudo-code, but it looks like valid SQL to me. The only question here is whether the code will solve your logical requirements, which still aren't exactly clear to me.

    Have you tried running the query you posted, and if so, what was the result?

  7. #7
    SitePoint Zealot //andrew-p//'s Avatar
    Join Date
    Dec 2002
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have had problems with MIN() and MAX() in Access before, which really drove me nuts...if you find anything, please post it.
    "When the ship misses the harbor, it is seldom the fault of the ship, and it is never the fault of the harbor..."
    //think of that when you get an error

  8. #8
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ive decided to flag trying to get all the info in one query and do seperate querys for each of the course offerings

    Code:
    // so i loop through here to retrive the list of id's i need
    <cfquery name="courseofferinglist" datasource="#request.dsn#">
    SELECT
      co.courseoffering_id, l.name AS location
    FROM
      courseoffering co, location l
    WHERE
      co.location_id = l.location_id	
    AND
      co.course_id = #URL.course_id#
    </cfquery>
    
    <cfloop query="courseofferinglist">
      // and so i run through each row in the first query and run
      // this query to get the min and max dates
      <cfquery name="courseofferinglist" datasource="#request.dsn#">
      SELECT
        MIN(starttime) AS startdate, 
        MAX(endtime) AS enddate
      FROM
        courseofferingdates
      WHERE
        courseofferingdates.courseoffering_id = #courseoffering_id#
      </cfquery>
    </cfloop>

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no no no!!! don't ever do a query inside a loop! there's always a better way!

    all you need here is another join and a GROUP BY clause, and you can do everything in one call to the database
    Code:
    select co.courseoffering_id
       ,   l.name			   as location
       ,   min(cod.date)		as startdate
       ,   max(cod.date)		as enddate 
      from courseoffering  co
    inner
      join location l
       on  co.location_id = l.location_id
    inner
      join courseofferingdates cod
       on  co.courseoffering_id = cod.courseoffering_id
     where co.course_id = #URL.course_id#
    group
       by  co.courseoffering_id
       ,   l.name

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers Rudy, you rock


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
  •