select
di_id as id,
di_ragione as ragione_sociale,
(select ba_nome from bandiere where ba_id = di_bandiera) as bandiera,
trim(concat(di_indirizzo_nocivico, ' ', di_civico)) as indirizzo,
right(concat('00000', di_cap), 5) as cap,
(select co_nome from pb_new.comuni where co_id = di_comune) as comune,
round(di_lat, 4) as lat, round(di_long, 4) as lng,
di_tel as telefono,
servito as servito,
self as self,
self24 as self24,
verde as benzina,
diesel as diesel,
gpl as gpl,
metano as metano,
(select re_price from reports where re_distributore = di_id and re_prodotto2 = 'benzina' order by re_date desc limit 1) as prezzo_benzina,
(select re_price from reports where re_distributore = di_id and re_prodotto2 = 'diesel' order by re_date desc limit 1) as prezzo_diesel,
(select re_price from reports where re_distributore = di_id and re_prodotto2 = 'gpl' order by re_date desc limit 1) as prezzo_gpl,
(select re_price from reports where re_distributore = di_id and re_prodotto2 = 'metano' order by re_date desc limit 1) as prezzo_metano,
(select re_date from reports where re_distributore = di_id and re_prodotto2 = 'benzina' order by re_date desc limit 1) as data_benzina,
(select re_date from reports where re_distributore = di_id and re_prodotto2 = 'diesel' order by re_date desc limit 1) as data_diesel,
(select re_date from reports where re_distributore = di_id and re_prodotto2 = 'gpl' order by re_date desc limit 1) as data_gpl,
(select re_date from reports where re_distributore = di_id and re_prodotto2 = 'metano' order by re_date desc limit 1) as data_metano
from distributori
where di_id > 1000 and di_lat > 0
order by di_id
