SitePoint Sponsor |
|
User Tag List
Results 1 to 10 of 10
-
May 15, 2003, 17:01 #1
- 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
-
May 17, 2003, 05:45 #2
- 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
-
May 18, 2003, 14:01 #3
- Join Date
- May 2003
- Location
- Auckland
- Posts
- 309
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
what needs a better explanation?
-
May 18, 2003, 16:27 #4
- Join Date
- Jan 2003
- Posts
- 38
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
May 18, 2003, 16:44 #5
- 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
-
May 19, 2003, 08:35 #6
- Join Date
- Jan 2003
- Posts
- 38
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
May 19, 2003, 12:27 #7
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
-
May 20, 2003, 21:09 #8
- 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>
-
May 21, 2003, 05:27 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
May 21, 2003, 14:17 #10
- Join Date
- May 2003
- Location
- Auckland
- Posts
- 309
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Cheers Rudy, you rock
Bookmarks