The easiest way I've seen to do this is to have a "most recent update" date field in the "events" or "documents" table. An insert/update trigger on the "updates" table can guarantee that the "most recent update" field will always be accurate. Then, all you need is a query something like this:
select e.*, u.* from events e, updates u order by e.mostRecentUpdate DESC, u.updateDate DESC The only problem with this approach is that you're storing redundant information. On the other hand, it makes the query itself dead simple and very fast. I think the tradeoff is worth it. >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: >> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:253675 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
