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 'รหัสอาชีพไม่สัมพันธ์อายุ',
if(getAgeYearNum(p.birth,CURDATE())<18 and p.marystatus!=1,'/',null)as 'สถานะสมรสไม่สัมพันธ์กับอายุ'
FROM person p
INNER JOIN ctitle ON ctitle.titlecode = p.prename
INNER JOIN coccupa ON p.occupa = coccupa.occupacode
WHERE
(p.nation='99' and cid13Chk(p.idcard)='f') or
p.hcode is null 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
(getAgeYearNum(p.birth,CURDATE())<15 and (p.fatherid is null or p.fatherid='0000000000000'))or
(getAgeYearNum(p.birth,CURDATE())<15 and (p.motherid is null or p.motherid='0000000000000'))or
p.dischargetype is null or
((p.dischargetype in (1,2,3) and p.dischargedate is null) or (p.dischargetype=9 and p.dischargedate is not null)) or
p.bloodgroup is 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'))
or (getAgeYearNum(p.birth,CURDATE())<18 and p.marystatus!=1)
group by p.pid;