Complex Query

Posts   
 
    
DaveR
User
Posts: 43
Joined: 15-Jun-2004
# Posted on: 27-Sep-2004 17:55:52   

I am wondering if the following query can be done entirely in code (perhaps using a TypedList), or if I need to resort to a TypedView.

In a nutshell, my DB contains documents, each which contains multiple sections, and each section contains multiple events. I want to return the latest event of a certain type among all of the sections of a particular document.

SELECT MAX(EventTime) AS LatestEventTime,LatestEditSect.DocumentID FROM
 (SELECT MAX(EventTime) AS SectEventTime,DocSectionID
  FROM DocSectionEvent
  WHERE DocSectionEventTypeID IN (8,12)
  GROUP BY DocSectionID) LatestSectEditEvent
 INNER JOIN DocSection LatestEditSect ON LatestSectEditEvent.DocSectionID=LatestEditSect.DocSectionID
 GROUP BY LatestEditSect.DocumentID
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 27-Sep-2004 19:01:39   

The virtual table you define in the first FROM clause can't be represented in code. I'm not entirely sure why it is required, perhaps it can be rewritten.

Frans Bouma | Lead developer LLBLGen Pro