Matt Something like this may help you then
(results at http://www.fclounge.cfdeveloper.co.uk/test.cfm ) <!--- get all event details ---> <cfquery name="getAllEvents" datasource="#request.dsn#" cachedwithin="#createTimeSpan(0,1,0,0)#" result="a"> select * from tblEventName evName inner join tblEventDate evDate on evName.eventID = evDate.eventID </cfquery> <!--- get the order of the events ordered by the ones that have the furthest dates first ---> <cfquery name="getOrder" datasource="#request.dsn#" cachedwithin="#createTimeSpan(0,1,0,0)#" result="b"> select eventname,tbleventdate.eventid, max(eventdate) as tempdate from tbleventdate inner join tbleventname on tbleventdate.eventid = tbleventname.eventid group by eventname, tbleventdate.eventid order by max(eventdate) desc, tbleventdate.eventid </cfquery> <!--- test debug ---> <cfdump var="#getAllEvents#"> <cfdump var="#a#"> <cfdump var="#getOrder#"> <cfdump var="#b#"> <cfset cfcQuery = queryNew("eventDateID, eventId, eventName, eventDate")> <cfset temp = queryAddRow(cfcQuery, getAllEvents.recordCount)> <Cfset rowCounter = 0> <!--- output the query with the order of correct eventids ---> <cfoutput query="getOrder"> <h5>#eventname#</h5> <!--- do a QoQ to get the eventDate details for this event id ---> <cfquery dbtype="query" name="getEventDetails"> select eventDateID, eventid, eventname, eventDate from getAllEvents where eventID = #eventID# order by eventDate desc </cfquery> <!--- output all events dates for this event id ---> <cfloop query="getEventDetails"> <cfset rowCounter = rowCounter + 1> <cfset temp = querySetCell(cfcQuery, "eventDateId", rowCounter, rowCounter)> <cfset temp = querySetCell(cfcQuery, "eventId", eventId, rowCounter)> <cfset temp = querySetCell(cfcQuery, "eventDate", eventDate, rowCounter)> <cfset temp = querySetCell(cfcQuery, "eventName", eventName, rowCounter)> #dateformat(eventDate,"m/dd/yyyy")#<br /> </cfloop> </cfoutput> <cfdump var="#cfcQuery#"> Rich On 9/20/06, David Low <[EMAIL PROTECTED]> wrote: > The test page works OK with a "www." added: > > http://www.fclounge.cfdeveloper.co.uk/test.cfm > > > -----Original Message----- > > From: Matt Williams [mailto:[EMAIL PROTECTED] > > Sent: 20 September 2006 14:59 > > To: CF-Talk > > Subject: Re: How to get these query results - Take 2 > > > > Rich, you are right in that I want the event with the furthest/newest > > date to show first. But if there is another date associated with that > > newest event, it should also show under its title. Your solution looks > > like it will work. I'll give it a try and let you know. > > > > I am hoping to find a "bit more clever" solution too as this is > > actually an OO-style app where I hope to have a CFC return the query > > results for me. I suppose I could just build a new query with QueryNew > > instead of the output as you show. > > > > BTW, your test.cfm page wouldn't load for me. Server not found error. > > > > Thanks, > > Matt > > > > On 9/20/06, RichL <[EMAIL PROTECTED]> wrote: > > > Matt > > > > > > You can try the following. It seems to work... my gut feeling tells > me > > > there may be an easier way with less queries but I can't think of > > > anything right now. You may want to consider caching queries for > speed > > > depending on how much the data is changing. > > > > > > <!--- get all event details ---> > > > <cfquery name="getAllEvents" datasource="#request.dsn#" > > > cachedwithin="#createTimeSpan(0,1,0,0)#" result="a"> > > > select * > > > from tblEventName evName > > > inner join tblEventDate evDate > > > on evName.eventID = evDate.eventID > > > </cfquery> > > > > > > <!--- get the order of the events ordered by the ones that have the > > > furthest dates first ---> > > > <cfquery name="getOrder" datasource="#request.dsn#" > > > cachedwithin="#createTimeSpan(0,1,0,0)#" result="b"> > > > select eventname,tbleventdate.eventid, max(eventdate) as > > tempdate > > > from tbleventdate > > > inner join tbleventname > > > on tbleventdate.eventid = tbleventname.eventid > > > group by eventname, tbleventdate.eventid > > > order by max(eventdate) desc, tbleventdate.eventid > > > </cfquery> > > > > > > <!--- test debug ---> > > > <cfdump var="#getAllEvents#"> > > > <cfdump var="#a#"> > > > <cfdump var="#getOrder#"> > > > <cfdump var="#b#"> > > > > > > <!--- output the query with the order of correct eventids ---> > > > <cfoutput query="getOrder"> > > > > > > <h5>#eventname#</h5> > > > > > > <!--- do a QoQ to get the eventDate details for this event > id -- > > -> > > > <cfquery dbtype="query" name="getEventDetails"> > > > select eventDate > > > from getAllEvents > > > where eventID = #eventID# > > > order by eventDate desc > > > </cfquery> > > > > > > <!--- output all events dates for this event id ---> > > > <cfloop query="getEventDetails"> > > > #dateformat(eventDate,"m/dd/yyyy")#<br /> > > > </cfloop> > > > > > > </cfoutput> > > > > > > You can see the results here which I think is where you are trying > to > > get to? : > > > > > > http://fclounge.cfdeveloper.co.uk/test.cfm > > > > > > My feeling is that there is something a bit more clever can be done > > > with SQL/grouping but this may give you something to work with ? > > > > > > Rich > > > > > > On 9/20/06, Matt Williams <[EMAIL PROTECTED]> wrote: > > > > I'm gonna try this again as while I slept last night my previous > post > > > > turned into a discussion of SQL best practices and did not give > the > > > > answer needed. But that's probably because I didn't explain well > > > > enough. Ordering by EventID will not work. Nor will ordering by > date. > > > > Maybe a little more data will help. > > > > > > > > EventID, EventName > > > > 1 | TestEventOne > > > > 2 | TestEventTwo > > > > 3 | AThirdEvent > > > > > > > > EventID | EventDates > > > > 1 | 9/10/2006 > > > > 1 | 9/20/2006 > > > > 2 | 9/15/2006 > > > > 3 | 9/30/2006 > > > > > > > > <!--- Desired results ---> > > > > AThirdEvent > > > > 9/30/2006 > > > > > > > > TestEventOne > > > > 9/20/2006 > > > > 9/10/2006 > > > > > > > > TestEventTwo > > > > 9/15/2006 > > > > > > > > If you order by EventID, you would get 1, 2, 3. If you ordered by > Date > > > > DESC, you would get > > > > 3 > > > > 1 > > > > 2 > > > > 1 > > > > But I want the TestEventOne outputs to be together. GroupBy won't > do > > > > it because you've ordered by date. > > > > > > > > This probably isn't possible without at least two queries, or at > least > > > > some QofQ usage. I just can't seem to get my head around the > solution > > > > though. > > > > > > > > -- > > > > Matt Williams > > > > "It's the question that drives us." > > > > > > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:253610 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
