How to relate pm_project table with project_status?

[Doug Ellis1]

I've been asked to create a simple report that lists all active projects in a portfolio (pm_project) along with the latest executive summary and status date from the project_status table.

Since reports do only full joins, I believe this needs to be done by creating a database view with a left outer join from the pm_project table to get all those records and only those records from the project_status table for that project.  

My problem is I can't find the field to link the two.  project.number would be ideal but I can't dot walk in the join.  

Does anyone know how to accomplish this? 

[Mike Allen · Accepted solution]

Your where should be:

WHERE project_status.project = pm_project.sys_id

No?

[Doug Ellis1]

Thanks Mike.  Now a twist. Is there a way to show every project only once with the latest executive summary for each?  I tried adding project_status_status_date = max(project_status_date) but got no results. 

[Charmagne Telli]

I have the same question on how to filter to get just the latest status report for my reporting. Did you figure out how? Can you share how you did it? Thanks.