select tm.libelle as libelleMandat, tm.codetypemandat as codeMandat, l.id as idLot, l.honorairesedl as montantEdl, l.honoraireslocation as montantHonoLoc, 
l.loyer as loyer, l.charges as charges, l.depotgarantie as garantie, l.datedisponibilite as dateDispo, rf.libelleregime as regimeFiscal, rf.code as codeRegime, 
tc.libelle as typeChauffage, tl.libelle as typeLot, t.nom as nomBailleur, t.prenom as prenomBailleur, p.id as idBailleur 
from lot l 
inner join assoc_mandat_lot aml on l.id = aml.lot_id 
and (aml.supprime = false or aml.supprime is null) 
and (aml.date_debut_juridique <= current_date ) 
and ( aml.date_fin_juridique is null or aml.date_fin_juridique > current_date) 
inner join mandat m on aml.mandat_id = m.id 
and (m.supprime = false or m.supprime is null) 
inner join type_mandat tm on m.type_mandat_id = tm.id 
and (tm.supprime = false or tm.supprime is null) 
inner join regime_fiscal rf on l.regime_fiscal_id = rf.id 
inner join descriptif_lot dl on l.descriptif_id = dl.id 
inner join descriptif_type_chauffage tc on dl.type_chauffage_id = tc.id 
inner join type_lot tl on l.type_lot_id = tl.id 
inner join profil p on m.profil_bailleur_id = p.id 
inner join tiers t on p.tiers_id = t.id 
where l.referencelot = '261411120305'
and (l.supprime = false or l.supprime is null) 
order by aml.date_debut_juridique desc 
limit 1;

Le point bloquant est généralement : 

inner join regime_fiscal rf on l.regime_fiscal_id = rf.id