SQL help needed for my data model

Here is my data model

ProjectsTable   DocumentTable   DocumentVersionsTable   UserTable   DocumentVersionActivityTable
ProjectID(PK)   DocumentID(PK)  DocumentVersionID(PK)   UserID      ActivityID(PK)
                ProjectID(FK)   DocumentID(PK)                      DocumentVersionID(FK)
                                                                    UserID(FK)
                                                                    DateOfActivity

What need to get is all of the distinct Projects where a specific user created a DocumentVersionActivity, sorted by DateOfActivity descending (i.e. so that the most recent document versions edited, for example, will reflect as a Project recently edited.

I've used the following, which successfully tracks the DocumentVersion to the Project, but I'm not sure how take it to the next step by limiting it by User and sorting based on the DocumentVersionActivityTable

select a.projectid 
   b.documentid 
   c.documentversionid 
from DocumentVersions c 
       join Documents b on b.ID = c.DocumentID 
       join Projects a on a.ID = b.ProjectID

I'm doing this for a ASP.Net Web API project using EF 6.0 so if I can capture these as easily or faster without SQL, I'd love to see that too.

Thanks.

FINAL EDIT: The code that I'm going with (note: keep commented out items for information). Thanks Leslie:

SELECT a.ID as projectid ,
  -- b.ID as haldocumentid, 
 --  c.ID as documentversionid,
   --d.projectcomponentActivityID as projectcomponentactivityid,
 --  e.id as userid,
   max(d.datetimeofactivity) as latestdate
FROM DocumentVersions c 
   INNER JOIN halDocuments b ON b.ID = c.DocumentID 
   INNER JOIN Projects a ON a.ID = b.ProjectID
   INNER JOIN Documentversionprojectcomponentactivities d ON 
   d.AssociatedDocumentVersionID = c.ID
   INNER JOIN Users e ON e.id = d.userid
WHERE e.id = 3 and d.DateTimeOfActivity >= DATEADD(d, -30, getdate())
group by a.id --, b.id, c.id, e.id
order by latestdate desc`

Answers


Edited:

SELECT 
  --a.ID as projectid, 
  b.ID as documentid, 
  c.ID as documentversionid,
  d.projectcomponentActivityID as projectcomponentactivityid,
  e.id as userid,
  MAX(d.DatetimeOfActivity)
FROM DocumentVersions c 
  INNER JOIN Documents b ON b.ID = c.DocumentID 
  --INNER JOIN Projects a ON a.ID = b.ProjectID
  INNER JOIN Documentversionprojectcomponentactivities d 
    ON d.AssociatedDocumentVersionID = c.ID
  INNER JOIN Users e ON e.id = d.userid
WHERE e.id = 2
GROUP BY 
  --a.ID as projectid, 
  b.ID as documentid, 
  c.ID as documentversionid,
  d.projectcomponentActivityID as projectcomponentactivityid,
  e.id as userid
--ORDER BY d.DatetimeOfActivity DESC

Need Your Help

CancelIO last over a minute when removing the HID device

visual-c++ barcode-scanner hid

I am working in a module for my application to be able to read from a HID barcode reader device.

C# Webforms User controls in VB.NET

c# asp.net vb.net webforms webusercontrol

I have an existing VB.NET web project that requires some new screens (webforms). Due to a number of reasons these new screens are to be developed in C#. Having had a read around it appears that thi...