How to limit by multiple categories(dynamically taken) in one SQL statement?

Question

Here is current SQL: http://www.copypastecode.com/22205/

It takes 70 new rows from table ads.

But it doesn't take proper amount(for example 5) of rows for each mr.region_id from joined table map_regions.

For example if I will add 50 ads in one region, it will take all 50 of them and leave 20 slots for the rest regions.

Please help me to upgrade current SQL, so it would take 5 rows from table ads for each mr.region_id in map_regions table.

Answer 1

Try

SELECT a.id,a.country,a.region,a.rajons,a.town AS town_id,a.pagasts,i.t0_1, mt.town, mp.pagasts, c.link
FROM ads a 
INNER JOIN categories c ON a.category = c.cat_id
RIGHT JOIN images i ON a.id = i.ad_id
LEFT JOIN (select * from map_regions where region_id = a.region LIMIT 5) as mr
LEFT JOIN map_towns mt ON a.town = mt.town_id
LEFT JOIN map_pagasts mp ON a.pagasts = mp.pagasts_id
WHERE a.expire >= ' . $current->get() . '
ORDER by a.id DESC
LIMIT 70