Limit LEFT JOIN Subquery to 1 result
The question below seems to LIMIT all the results when it's LEFT JOINed, so the total in the subquery is 1. How can I make it LIMIT to get a match 1:1
between rows projects
and the last date is stored in projects_hours_archive
which stores the records projects.projected_hours
that are collected on cron job once a week?
projected_hours_archive
It 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 using MySQL Limit LEFT JOIN Subquery after joining , but was not successful. If I delete LIMIT
in a subquery, I get too many results.
source to share
use group by
in subquery and get max date for each project.
EDIT: As per the OP's comment, adding a second maximum date.
Used a trick from mysql how to get the 2nd highest value with the group on the left join and on the left.
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
source to share