select d.durum_ad,u.unvan_ad,it.izin_ad,p.personel_isegiristarih,p.personel_adsoyad,sum(i.izin_suresi) as izin_suresi,i.izin_yil,ich.izin_hakki, (ich.izin_hakki - sum(i.izin_suresi)) as Kalanizin, /*TIMESTAMPDIFF(YEAR, p.personel_isegiristarih, CURDATE()) AS tecrube,*/ (i.izin_yil - YEAR(p.personel_isegiristarih)) as tecrube from izin_calisan_haklari ich LEFT OUTER JOIN durum d on d.durum_id=ich.calisan_statu_id LEFT OUTER JOIN personel p on p.unvan_id=d.durum_id LEFT OUTER JOIN izin i on i.izin_personel=p.personel_id and ich.izin_tur_id=i.izin_turid LEFT OUTER JOIN unvan u on u.unvan_id=p.personel_unvan LEFT OUTER JOIN izin_turleri it on it.izin_turid=ich.izin_tur_id /*where ich.alt_tecrube<=TIMESTAMPDIFF(YEAR, p.personel_isegiristarih, CURDATE()) and ich.ust_tecrube>=TIMESTAMPDIFF(YEAR, p.personel_isegiristarih, CURDATE())*/ where ich.alt_tecrube<= i.izin_yil - YEAR(p.personel_isegiristarih) and ich.ust_tecrube>= i.izin_yil - YEAR(p.personel_isegiristarih) and i.izin_durum='1' and p.personel_durum='1' and i.izin_yil>=NOW() - INTERVAL 3 year /*son 3 yılın kayıtlarını getirir*/ GROUP BY i.izin_personel,i.izin_yil,i.izin_turid order by personel_adsoyad ASC