SELECT a.id as "id_ad", esg.`id_subgroup`, esg.`chat`, esg.`webinar`, e.id_employer, e.titolo as "nome_azienda", p.id_profile as "id_profilo", a.titolo as "nome_annuncio", LOWER(p.name) as "nome_profilo", a.`in_italia`, a.`id_stato_lavoro`, id_regione_lavoro, id_prov_lavoro, id_comune_lavoro, stato.nome AS "stato", regione.nome AS "regione", prov.nome AS "provincia", prov.sigla, comune.nome AS "comune", IFNULL(es.stand, "") as "stand", IF((SELECT DISTINCT id_ads FROM apl_ads_catprotette cat WHERE cat.id_ads = a.id AND cat.id_categoria IN (1,5)), "true", "false") AS "isH", esg.modalita_partecipazione, CASE a.in_italia = 1 WHEN TRUE THEN CASE a.id_regione_lavoro > 0 WHEN TRUE THEN CASE a.`id_prov_lavoro` > 0 WHEN TRUE THEN CASE a.`id_comune_lavoro` > 0 WHEN TRUE THEN IF(comune.nome = prov.nome, comune.nome, CONCAT(comune.`nome`,' (',prov.sigla,')')) ELSE CONCAT("Provincia di ", prov.`nome`) END ELSE regione.nome END ELSE 'Italia' END END AS 'zona_italia', CASE id_stato_lavoro IS NOT NULL WHEN TRUE THEN IF(a.id_stato_lavoro IS NOT NULL, stato.nome, '') ELSE '' END AS 'zona_estero' FROM `apl_ads` a JOIN apl_employers e ON a.id_employer = e.id_employer JOIN apl_emp_man_jusers emj ON (emj.id_employer = e.id_employer AND emj.active=1 AND emj.id_manifestazione=24) JOIN apl_employers_subgroups esg ON (esg.id_employer = e.id_employer) JOIN apl_subgroups s ON (esg.id_subgroup = s.id_subgroup AND s.id_manifestazione = 24) LEFT JOIN `i15_apl_employers_stand` es ON (es.id_employer = e.id_employer AND es.id_manifestazione=24) JOIN CP2011 p ON p.id_profile = a.id_profile LEFT JOIN `apl_stati` stato ON (stato.`id` = a.id_stato_lavoro) LEFT JOIN `apl_regioni` regione ON (regione.`id` = a.`id_regione_lavoro`) LEFT JOIN `apl_province` prov ON (prov.`id` = a.`id_prov_lavoro`) LEFT JOIN `apl_comuni` comune ON (comune.`id` = a.`id_comune_lavoro`) WHERE a.id_manifestazione = 24 AND a.validato=1 AND a.active=1 AND a.id IN ( SELECT id_ads FROM apl_ads_catprotette cat WHERE cat.id_ads = a.id AND cat.id_categoria IN (1,5)) ORDER BY p.name