How to fetch all rows with left join getting the latest row in second table

Question

I am trying to follow the instructions in this answer how to get the latest row in the joined table.

I have two tables.

Projects : id, title
Status : project_id, status_id, created(DATETIME)

When I know the project ID (example = 2) I have the correct query to select the latest status update.

SELECT projects. * , project_state_project_map.status_id AS status, 
project_state_project_map.created AS status_created
FROM projects
LEFT JOIN (

    SELECT * 
    FROM project_state_project_map
    WHERE project_id = 2
    ORDER BY created DESC 
    LIMIT 1
    )
project_state_project_map ON project_state_project_map.project_id = projects.id
WHERE projects.id = 2
LIMIT 1

However, I cannot figure out how to select all projects with their current status. What do I have to change to the sql to get all projects with their latest states.

Taryn · Accepted Answer · 2013-03-06T14:23:02.707

1

I would suggest altering your query to use an aggregate function to get the latest date with status:

SELECT p. *, 
  pm1.status_id AS status, 
  pm1.created AS status_created
FROM projects p
LEFT JOIN project_state_project_map pm1
  ON pm1.project_id = p.id
INNER JOIN 
(
  SELECT max(created) MaxDate, project_id
  FROM project_state_project_map
  WHERE project_id = 2
  GROUP BY project_id
) pm2 
  ON pm1.project_id = pm2.project_id
  AND pm1.created = pm2.MaxDate
WHERE p.id = 2

This gets the max(created) date for each project, then this result is used to return the status with that date.

This could also be written as:

SELECT p. *, 
  pm.status_id AS status, 
  pm.created AS status_created
FROM projects p
LEFT JOIN
(
  SELECT pm1.project_id,
    pm1.status_id,
    pm1.created
  FROM project_state_project_map pm1
  INNER JOIN 
  (
    SELECT max(created) MaxDate, project_id
    FROM project_state_project_map
    WHERE project_id = 2
    GROUP BY project_id
  ) pm2
    ON pm1.project_id = pm2.project_id
    AND pm1.created = pm2.MaxDate
) pm
  ON pm.project_id = p.id
WHERE p.id = 2;

edited Mar 06 '13 at 14:23

answered Mar 06 '13 at 14:15

Taryn

242,637
56
362
405

Thanks Bluefeet. But `a)` this returns all states for one project id. I am looking for only the latest state. `b)` How can I get all the projects – Zevi Sternlicht Mar 06 '13 at 14:20
The second query works well. Just missing comma in subselect, thanks loads. Now, how to get all projects, not just by id? – Zevi Sternlicht Mar 06 '13 at 14:22
@InGodITrust You can remove the `WHERE project_id = 2` from the subquery and it will return the records for all projects. – Taryn Mar 06 '13 at 14:23
Would it be much cleaner to just store a primary ID of the status row in the projects table and use a simple join rather then this complicated business? – Zevi Sternlicht Mar 06 '13 at 20:18
@InGodITrust well that depends, the status will change on different dates. Don't you want to know when the status changes? If so, then you will still have to store in separate tables. – Taryn Mar 06 '13 at 20:20
I will still keep the second table, but I will add a PRIMARY key to each row which I will refer to in my projects table. Then i can join the status table and using the ID get the date of the status update. Is this a better way? – Zevi Sternlicht Mar 06 '13 at 20:23
@InGodITrust I think you will have to post a new question with your current structure and then the possible new structure. It will be easier then going back and forth in the comments. :) – Taryn Mar 06 '13 at 20:26
check out http://stackoverflow.com/questions/15257889/should-i-create-a-new-field-in-the-table-or-just-select-the-max-of-the-second-ta – Zevi Sternlicht Mar 06 '13 at 20:58

How to fetch all rows with left join getting the latest row in second table

1 Answers1

Linked