select
person.pid,
person.fname,
person.lname,
person.sex,
person.idcard,
house.hno,
village.villno,
person.marystatus,
women.fptype as 'วิธีคุมกำเนิดปัจจุบัน',
women.reasonnofp as 'สาเหตุที่ไม่คุมกำเนิด',
women.totalson as 'จำนวนบุตรทั้งหมด(รวมตาย)',
women.childalive as 'จำนวนบุตรที่มีชีวิต',
women.abortion as 'จำนวนการแท้งบุตร',
women.stillbirth as 'จำนวนทารกตายในครรภ์'
FROM person
inner join house on person.pcucodeperson = house.pcucode and person.hcode = house.hcode
inner join village on house.pcucode = village.pcucode and house.villcode = village.villcode
left join women on person.pcucodeperson = women.pcucodeperson and person.pid = women.pid
where
getAgeYearNum(person.birth,concat(year(curdate()),'-08-01')) between 15 and 49
and person.sex=2
and person.marystatus in ('2','f')
and person.typelive in (0,1,3)
and person.dischargetype=9
and (women.fptype is null
or (women.fptype=9 and women.reasonnofp is null)
or women.totalson is null
or women.childalive is null
or women.abortion is null
or women.stillbirth is null)
group by person.pid
order by village.villno,house.hno*1;