Comitati Regionali


SELECT C.id,C.denom,C.denom as regione, concat(C.ind,ifnull(concat(', ', IC.nomeC),'')) as ind, C.tel, C.fax,C.www, C.email ,
        concat(P.cognome, ' ',  P.nome) as Presidente,
        concat(P2.cognome, ' ',  P2.nome) as vicepresidente_judo,
        concat(P3.cognome, ' ',  P3.nome) as vicepresidente_lotta,
        concat(P4.cognome, ' ',  P4.nome) as vicepresidente_karate,
        concat(P6.cognome,' ',P6.nome) as fiduciario_aikido,
        concat(P7.cognome,' ',P7.nome) as fiduciario_fujitsu,
        concat(P8.cognome,' ',P8.nome) as fiduciario_mga,
        concat(P9.cognome,' ',P9.nome) as fiduciario_sumo,
         group_concat(distinct concat(P10.cognome,' ',P10.nome)) as delegati,
         area
FROM   01_comitati C
                              inner join ISTAT_comuni IC on C.comune=IC.CodC
        left join
        01_consiglio_comitato CC on C.id=CC.id_comitato and CC.id_qualifica=1  and CC.anno=Year(Now())
        left join 01_persone P on CC.id_persona=P.id
        left join
        01_consiglio_comitato CC2 on C.id=CC2.id_comitato and CC2.id_qualifica=2  and CC2.anno=Year(Now())
        left join 01_persone P2 on CC2.id_persona=P2.id
        left join
        01_consiglio_comitato CC3 on C.id=CC3.id_comitato and CC3.id_qualifica=3  and CC3.anno=Year(Now())
        left join 01_persone P3 on CC3.id_persona=P3.id
        left join
        01_consiglio_comitato CC4 on C.id=CC4.id_comitato and CC4.id_qualifica=4  and CC4.anno=Year(Now())
        left join 01_persone P4 on CC4.id_persona=P4.id
        left join
        01_consiglio_comitato CC5 on C.id=CC5.id_comitato and CC5.id_qualifica=7  and CC5.anno=Year(Now())
        left join 01_persone P5 on CC5.id_persona=P5.id
        left join
        01_consiglio_comitato CC6 on C.id=CC6.id_comitato and CC6.id_qualifica=9  and CC6.anno=Year(Now())
        left join 01_persone P6 on CC6.id_persona=P6.id
        left join
        01_consiglio_comitato CC7 on C.id=CC7.id_comitato and CC7.id_qualifica=10  and CC7.anno=Year(Now())
        left join 01_persone P7 on CC7.id_persona=P7.id
        left join
        01_consiglio_comitato CC8 on C.id=CC8.id_comitato and CC8.id_qualifica=11  and CC8.anno=Year(Now())
        left join 01_persone P8 on CC8.id_persona=P8.id
        left join
        01_consiglio_comitato CC9 on C.id=CC9.id_comitato and CC9.id_qualifica=12  and CC9.anno=Year(Now())
        left join 01_persone P9 on CC9.id_persona=P9.id
        left join
        01_consiglio_comitato CC10 on C.id=CC10.id_comitato and CC10.id_qualifica=7  and CC10.anno=Year(Now())
        left join 01_persone P10 on CC10.id_persona=P10.id
WHERE (C.id!=0) AND (C.denom NOT LIKE 'DELEGA%' AND C.denom NOT LIKE 'DELAGA%')
GROUP BY C.id


SELECT C.id,C.denom,C.denom as regione, concat(C.ind,ifnull(concat(', ', IC.nomeC),'')) as ind, C.tel, C.fax,C.www, C.email ,
        concat(P.cognome, ' ',  P.nome) as Presidente,
        concat(P2.cognome, ' ',  P2.nome) as vicepresidente_judo,
        concat(P3.cognome, ' ',  P3.nome) as vicepresidente_lotta,
        concat(P4.cognome, ' ',  P4.nome) as vicepresidente_karate,
        concat(P6.cognome,' ',P6.nome) as fiduciario_aikido,
        concat(P7.cognome,' ',P7.nome) as fiduciario_fujitsu,
        concat(P8.cognome,' ',P8.nome) as fiduciario_mga,
        concat(P9.cognome,' ',P9.nome) as fiduciario_sumo,
         group_concat(distinct concat(P10.cognome,' ',P10.nome)) as delegati,
         area
FROM   01_comitati C
                              inner join ISTAT_comuni IC on C.comune=IC.CodC
        left join
        01_consiglio_comitato CC on C.id=CC.id_comitato and CC.id_qualifica=1  and CC.anno=Year(Now())
        left join 01_persone P on CC.id_persona=P.id
        left join
        01_consiglio_comitato CC2 on C.id=CC2.id_comitato and CC2.id_qualifica=2  and CC2.anno=Year(Now())
        left join 01_persone P2 on CC2.id_persona=P2.id
        left join
        01_consiglio_comitato CC3 on C.id=CC3.id_comitato and CC3.id_qualifica=3  and CC3.anno=Year(Now())
        left join 01_persone P3 on CC3.id_persona=P3.id
        left join
        01_consiglio_comitato CC4 on C.id=CC4.id_comitato and CC4.id_qualifica=4  and CC4.anno=Year(Now())
        left join 01_persone P4 on CC4.id_persona=P4.id
        left join
        01_consiglio_comitato CC5 on C.id=CC5.id_comitato and CC5.id_qualifica=7  and CC5.anno=Year(Now())
        left join 01_persone P5 on CC5.id_persona=P5.id
        left join
        01_consiglio_comitato CC6 on C.id=CC6.id_comitato and CC6.id_qualifica=9  and CC6.anno=Year(Now())
        left join 01_persone P6 on CC6.id_persona=P6.id
        left join
        01_consiglio_comitato CC7 on C.id=CC7.id_comitato and CC7.id_qualifica=10  and CC7.anno=Year(Now())
        left join 01_persone P7 on CC7.id_persona=P7.id
        left join
        01_consiglio_comitato CC8 on C.id=CC8.id_comitato and CC8.id_qualifica=11  and CC8.anno=Year(Now())
        left join 01_persone P8 on CC8.id_persona=P8.id
        left join
        01_consiglio_comitato CC9 on C.id=CC9.id_comitato and CC9.id_qualifica=12  and CC9.anno=Year(Now())
        left join 01_persone P9 on CC9.id_persona=P9.id
        left join
        01_consiglio_comitato CC10 on C.id=CC10.id_comitato and CC10.id_qualifica=7  and CC10.anno=Year(Now())
        left join 01_persone P10 on CC10.id_persona=P10.id
WHERE (C.id!=0) AND (C.denom NOT LIKE 'DELEGA%' AND C.denom NOT LIKE 'DELAGA%')
GROUP BY C.id