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

---

???

what needs a better explanation?

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?

Thats not a real query. its pseudo sql/code

sorry if that was the source of confusion

DB: access

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?

I have had problems with MIN() and MAX() in Access before, which really drove me nuts...if you find anything, please post it.

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>

---

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

---

Cheers Rudy, you rock :)