I'm guessing you want this? SELECT * FROM courses A, classes B,sessions C WHERE A.course_id = B.course_id AND C.session_id = B.session_id AND endDate BETWEEN '#url.minDate#' AND '#url.maxDate#'
----- Original Message ----- From: <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, March 13, 2003 3:20 PM Subject: SQL: subquery and group by a non selected field > I am trying to return a list of ids in a sub query but have them > grouped by another column. > > View the subquery below. I am attempting to GROUP BY class_id > because I need 1 session_id (the one with the MAX(endDate)) from > each group of class_ids. > > SELECT * > ~FROM courses A, classes B,sessions C > ~WHERE A.course_id = B.course_id AND > ~~ C.class_id = B.class_id AND > ~C.session_id IN (SELECT session_id > ~~~~FROM sessions > ~~~~GROUP BY class_id > ~~~~HAVING MAX(endDate) BETWEEN '#url.minDate#' AND '#url.maxDate#') > > > Any ideas on how to get this to work? > > DM > > ___________________________________________________________ > Sent by ePrompter, the premier email notification software. > Free download at http://www.ePrompter.com. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
