limit left join

Recently we had to pull a single date from a table of dates during a left join. In fact, we needed the most recent date that matched a particular id. We quickly figured out that a left join does not allow for the use of limits in our case.

The Dilemma

We did mean in our case. There is definitely the possibility of using sub queries. In our case, the limiting factor depended on a variable not available to sub queries.

The Solution

The solution is simple. You need to use the order by and group by keywords. Order by the internal query to setup your single record at the top of the list. Then group by the external variable, which in our case was an ID.

[sql]

Select …
LEFT JOIN (
SELECT *
FROM equipment_dates
ORDER BY pressure_date DESC
) AS eqip_d ON eqip_d.equip_id = equiptable.id
WHERE …
GROUP BY equiptable.id

[/sql]