0

I have two Tables, one where Project Information is stored, and a separate one for Store Activity log on each project. I'm trying to create a SQL to retrieve 1 line from each project displaying the project Information and the latest project update only but I'm not being able to get that information.

My Tables are set up the following way:

Project Data Project_ID (PK) Project Name Project Status

Project Milestone Project_ID (FK to Project DATA) Milestone_Type DATE

I was trying the following query but without luck.

select PROJECT_DATA.PROJECT_NAME as PROJECT_NAME,
  PROJECT_DATA.PROJECT_STATUS as PROJECT_STATUS,
  PROJECT_MILESTONE.MILESTONE as MILESTONE,
  PROJECT_MILE`enter code here`STONE.DATE as DATE 
from PROJECT_MILESTONE PROJECT_MILESTONE
Left Join  PROJECT_DATA PROJECT_DATA  on PROJECT_DATA.ID=PROJECT_MILESTONE.PROJECT_ID 
where 
  PROJECT_MILESTONE.DATE = (
Select MAX (PROJECT_MILESTONE.DATE)
FROM PROJECT_MILESTONE
WHERE PROJECT_DATA PROJECT_DATA  on PROJECT_DATA.ID=PROJECT_MILESTONE.PROJECT_ID)
MT0
  • 143,790
  • 11
  • 59
  • 117
Sebastian
  • 1

1 Answers1

1

Table "setup" and query you posted differ so - I'm going to do the same - post query which should return data you need, but with my own table and column names.

select d.project_id, d.name, d.status, m.datum, m.type
from project_data d join milestone m on m.project_id = d.project_id
where m.datum = (select max(m1.datum)
                 from milestone m1
                 where m1.project_id = m.project_id
                );
Littlefoot
  • 131,892
  • 15
  • 35
  • 57