SELECT
p.pid as HN,
concat(p.fname,'  ',p.lname)as 'ชื่อ-สกุล',
if((getAgeYearNum(p.birth,CURDATE())<4 and p.occupa not in ('9002','000')) or (getAgeYearNum(p.birth,CURDATE()) between 4 and 15 and p.occupa not in ('9000','015')),'/',null)as 'รหัสอาชีพไม่สัมพันธ์อายุ'
FROM person p
INNER JOIN ctitle ON ctitle.titlecode = p.prename
INNER JOIN coccupa ON p.occupa = coccupa.occupacode
LEFT JOIN persondeath ON p.pid=persondeath.pid
WHERE
CONCAT(p.pid,p.pcucodeperson) NOT IN (select CONCAT(persondeath.pid,persondeath.pcucodeperson)from persondeath)
AND (p.nation='99' and cid13Chk(p.idcard)='f') or
p.prename is null or
p.fname is null or
p.lname is null or
(p.sex IS NULL OR p.sex not in('1','2')) or
p.birth IS NULL or
p.marystatus is null or
(coccupa.occupacode is null or LENGTH(coccupa.mapoccupa)!=4) or
p.nation is null or
p.religion is null or
p.educate is null or
p.dischargetype is null or
p.dischargetype=9 and p.dischargedate is not null or
(p.nation !='99' and p.intercode is null)OR
p.typelive is null OR
(getAgeYearNum(p.birth,CURDATE())<4 and p.occupa not in ('9002','000')) or (getAgeYearNum(p.birth,CURDATE()) between 4 and 15 and p.occupa not in ('9000','015'))
group by p.pid;