SELECT p.idcard
,c.titlename
,p.fname
,p.lname
,p.sex
,DATE_FORMAT(p.birth,'%d/%m/%Y') AS birth
,p.mumoi
,p.hnomoi
,CONCAT(p.provcodemoi,p.distcodemoi,p.subdistcodemoi) AS aid
,p.hosmain
,p.hossub
,p.nation
,'2' AS his
FROM person p
LEFT OUTER JOIN ctitle c ON p.prename = c.titlecode
WHERE p.mumoi > 0
AND (p.idcard IS NOT NULL OR p.idcard <> '')
AND p.typelive IN ('1','3')
AND LENGTH(CONCAT(p.provcodemoi,p.distcodemoi,p.subdistcodemoi))=6
AND p.pid NOT IN (select pid from persondeath);