SQL: Left outer join with conditions

I need to do a l开发者_C百科eft outer join like this:

SELECT 
    tblProjects.*, 
    tblNotes.NoteID,
    tblNotes.regDate AS lastUpdatedNote
FROM 
    tblProjects 
    LEFT OUTER JOIN tblNotes ON tblProjects.ProjectID = tblNotes.ProjectID

But a project can have several notes and in this case im only interested in the one with MAX(regDate) how can i add this condition to the sql above? Or do i have to grab all the notes for each project and filter out the latest in code?

So i only want one row per project and in that row i want the lastUpdatedNote info.

Thx :)

In MS SQL you can do something like this:

SELECT 
    tblProjects.*, 
    tblNotes.NoteID,
    tblNotes.regDate AS lastUpdatedNote
FROM tblProjects 
OUTER APPLY
(
    SELECT TOP 1 NoteId, regDate
    FROM tblNotes
    WHERE tblProjects.ProjectID = tblNotes.ProjectID
    ORDER BY tblNotes.regDate DESC
) tblNotes

Replace with CROSS APPLY if you need only those projects that have notes.

or with CTE:

WITH cte
AS
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY NoteId ORDER BY RegDate DESC) RowNumber
    FROM tblNotes
)
SELECT tblProjects.*, cte.NoteID, cte.RegDate
FROM tblProjects 
    LEFT JOIN cte on
        tblProjects .ProjectId = cte.ProjectId AND RowNumber = 1

Replace with INNER JOIN if you need only those projects that have notes.