Solved Query to also return null values (1 Viewer)

[mafhobb]

I have a table with a list of projects and a related table with the project history. I am now working on a simple query to bring up all projects and their last history comment.

SELECT tblProject.PropertyName, tblProject.ProjectName, tblProject.Status, tblProject.Involved, Last(tblProjectHistory.HistoryComment) AS LastOfHistoryComment
FROM tblProject INNER JOIN tblProjectHistory ON tblProject.ProjectID = tblProjectHistory.ProjectID
GROUP BY tblProject.PropertyName, tblProject.ProjectName, tblProject.Status, tblProject.Involved;

My issue is that I have projects which still do not have any history in them (sometimes it takes a while to start them or assign them) and this query does not bring up those projects.
How can I make this query show all projects, including the ones that do not have history?
Mafhobb

 

[CJ_London]

1. use a left join rather than an inner join
2. be aware that last does not mean latest. your projecthistory table should have a date field and this should be sorted ascending (to use last) or descending (to use first)

 

[mafhobb]

Like this?

SELECT tblProject.PropertyName, tblProject.ProjectName, tblProject.Status, tblProject.Involved, Last(tblProjectHistory.CommentDate) AS LastOfCommentDate, Last(tblProjectHistory.HistoryComment) AS LastOfHistoryComment
FROM tblProject LEFT JOIN tblProjectHistory ON tblProject.ProjectID = tblProjectHistory.ProjectID
GROUP BY tblProject.PropertyName, tblProject.ProjectName, tblProject.Status, tblProject.Involved;

 

[CJ_London]

As far as the join is concerned - yes

but you need to include

ORDER BY tblProjectHistory.CommentDate

 

[ebs17]

but you need to include
ORDER BY tblProjectHistory.CommentDate

Are you sure that's enough? Roughly speaking, the order of query execution is JOIN first, then grouping and aggregation, and then sorting.

A data record sequence when aggregating does not yet use this sorting instruction, but depends on the previous processes including their index use and, in case of doubt, cannot be determined. With subqueries (the much scolded ones) you can fix the order of a query processing yourself, because first of all the data sources are evaluated in the FROM part.

SELECT
   P.PropertyName,
   P.ProjectName,
   P.Status,
   P.Involved,
   Q.CommentDate,
   Q.HistoryComment
FROM
   tblProject AS P
      LEFT JOIN
         (
            SELECT
               PH.ProjectID,
               PH.CommentDate,
               PH.HistoryComment
            FROM
               (
                  SELECT
                     ProjectID,
                     MAX(CommentDate) AS MaxDate
                  FROM
                     tblProjectHistory
                  GROUP BY
                     ProjectID
               ) AS MD
                  INNER JOIN tblProjectHistory AS PH
                  ON MD.ProjectID = PH.ProjectID
                     AND
                  MD.MaxDate = PH.CommentDate) AS Q
                  ON P.ProjectID = Q.ProjectID

 

[mafhobb]

Are you sure that's enough? Roughly speaking, the order of query execution is JOIN first, then grouping and aggregation, and then sorting.

A data record sequence when aggregating does not yet use this sorting instruction, but depends on the previous processes including their index use and, in case of doubt, cannot be determined. With subqueries (the much scolded ones) you can fix the order of a query processing yourself, because first of all the data sources are evaluated in the FROM part.

SELECT
   P.PropertyName,
   P.ProjectName,
   P.Status,
   P.Involved,
   Q.CommentDate,
   Q.HistoryComment
FROM
   tblProject AS P
      LEFT JOIN
         (
            SELECT
               PH.ProjectID,
               PH.CommentDate,
               PH.HistoryComment
            FROM
               (
                  SELECT
                     ProjectID,
                     MAX(CommentDate) AS MaxDate
                  FROM
                     tblProjectHistory
                  GROUP BY
                     ProjectID
               ) AS MD
                  INNER JOIN tblProjectHistory AS PH
                  ON MD.ProjectID = PH.ProjectID
                     AND
                  MD.MaxDate = PH.CommentDate) AS Q
                  ON P.ProjectID = Q.ProjectID[/COD
[/QUOTE]

 

[mafhobb]

...I am not sure how to move on from this...

 

[CJ_London]

suggest try my suggestion first as it is more straight forward. Use some test data and check you get the correct data. If not go for Eberhard's suggestion - or go there straight away - either way you should have test data and check the result is as expected.

 

[mafhobb]

Thank you!