Q:

get records by the lowest rank with nested tables and group by - MYSQL

I have a query problem that I previously solved with 2 tables and different db structure, and now I have 3 tables and I cant get the right result
leads table - regular leads table with id, firstname, etc...

statuses table - id, lead_id, brand_id, map_id 
lead_id is relationship key for id in lead 
map_id is relationship key for map_keys table

map_keys table - id, name, rank

the rules: 
each lead can have more than one status or it can be without status
the query should print only the lowest status even if there is two or more statuses for the same lead 
example: 
lead: id = 3, 
statuses: id = 7, lead_id = 3, map_id = 5
and another record: 
statuses: id = 10, lead_id = 3, map_id = 1

map_keys: 
id = 5, rank = 5
id = 1, rank = 1 

in the result I should get 
lead_id = 3, map_id = 1 

In the past I had the rank within the statuses table, so my solution was
SELECT  IF(s2.rank IS NULL, s.rank, s2.rank) AS rank  FROM `leads` l 
LEFT JOIN statuses s ON s.lead_id = l.id
LEFT JOIN statuses s2 ON s2.lead_id = s.lead_id AND s.rank > s2.rank
GROUP BY l.id

but with the new db structure I cant get the right result, I hope that my question is understandable, If not I will try my best to explain it better, Thanks! 

A:

I solved my problem, my final query is 
SELECT * FROM leads                                                                                                 
LEFT JOIN (                                                                                                 
SELECT lead_id, mk.name, mk.rank FROM `statuses` s                                                                                                  
JOIN map_keys mk ON mk.id = s.map_id                                                                                                    
JOIN statuses_maps sm ON sm.id = s.status_id                                                                                                    
ORDER BY rank  ASC ) as s ON lead_id = lead.id                                                                                                  
GROUP BY leads.id   

