MySQL subquery limit 1

Question

I have an sql query that returns a list of residential units, and a subquery that is supposed to get the last entered bill for that unit.

However when I add LIMIT 1 to the subquery, no bill entries are returned? If I leave it out, I get duplicate unit rows depending on the number of bill for the unit.

select * from unit u
left join (select id as billId, unit_id, added_on, end_reading, bill_type from bills
order by id desc) b ON unit_id = u.id
where community_Id = 1
and unit_section = 7
and unit_floor in (1,2,3,4,5)
order by unit_floor, display_order asc;

Anyone know how I can the subquery result limited to 1 bill?

Answer 1

Think you will need a sub query to get the first (lowest) id for each unit_id from the bills table. Then use that to join between the unit and bills table, getting the other matching columns from bills for that lowest id

SELECT u.*, bills.*
FROM unit u
LEFT OUTER JOIN 
(
    SELECT unit_id, MIN(id) AS min_id
    FROM bills
    GROUP BY unit_id
) b ON b.unit_id = u.id
LEFT OUTER JOIN bills
ON b.unit_id = bills.unit_id
AND b.min_id = bills.id
WHERE u.community_Id = 1
AND u.unit_section = 7
AND u.unit_floor in (1,2,3,4,5)
ORDER BY u.unit_floor, u.display_order asc;