Limit a LEFT JOIN Subquery to 1 result

mysql, sql

Solution

use `group by` in the sub query and get maximum date per project.

EDIT: as per the OP comment, adding second max date.

The trick from this mysql how to get 2nd highest value with group by and in a left join is used.

SELECT
    GROUP_CONCAT( projected_hours_last.secondMaxDate, '|', projected_hours_last.number ) AS 'projected_last_info'
FROM
projects


LEFT JOIN (
    SELECT project_id, max(date) as maxDate,
           substring_index(substring_index(group_concat(date order by date desc), ',', 2), ',', -1
                            ) as secondMaxDate
    FROM
    projected_hours_archive
    group by project_id
) AS projected_hours_last ON ( projected_hours_last.project_id = projects.id )

WHERE projected_hours > 0

GROUP BY projects.id

Problem

The query below seems to LIMIT all the results when it's being LEFT JOINed, so the total in the subquery is just 1. How can I make it LIMIT so that I get a `1:1` match between `projects` rows and the last date stored in `projects_hours_archive` which stores records of `projects.projected_hours` that are collected on a cron job once per week? `projected_hours_archive` has columns: `id`, `project_id`, `hours` and `datetime`. ``` SELECT GROUP_CONCAT( projected_hours_last.date, '|', projected_hours_last.number ) AS 'projected_last_info' FROM projects LEFT JOIN ( SELECT * FROM projected_hours_archive ORDER BY date DESC LIMIT 1 ) AS projected_hours_last ON ( projected_hours_last.project_id = projects.id ) WHERE projected_hours > 0 GROUP BY projects.id ``` I tried to adopt using MySQL Limit LEFT JOIN Subquery after joining but wasn't successful. If I remove the `LIMIT` in the subquery I get too many results.

Original source

Related problems