SQL - using min and max to retrive rows

[Imminent]

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:

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

[weirdbeardmt]

???

[Imminent]

what needs a better explanation?

[rycamor]

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?

[Imminent]

Thats not a real query. its pseudo sql/code

sorry if that was the source of confusion

DB: access

[rycamor]

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?

[//andrew-p//]

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

[Imminent]

ive decided to flag trying to get all the info in one query and do seperate querys for each of the course offerings

// 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>

[r937]

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

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

[Imminent]

Cheers Rudy, you rock