SELECT
village.villno AS 'หมู่ที่',
village.villname AS 'ชื่อหมู่บ้าน',
person.pid AS 'HN',CONCAT(ctitle.titlename,person.fname,' ',person.lname) AS 'ชื่อ - นามสกุล' ,house.hno AS 'บ้านเลขที่',vacdTC.pid AS 'dTC',pvola.namevola AS 'อสม. ที่รับผิดชอบ'
FROM
person
INNER JOIN ctitle ON ctitle.titlecode = person.prename
INNER JOIN house ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode
INNER JOIN village ON village.pcucode = house.pcucode AND village.villcode = house.villcode
LEFT OUTER JOIN
(
SELECT ps.pcucodeperson,ps.pid AS pid_vola,CONCAT(ct.titlename,ps.fname," ",ps.lname) AS namevola,ps.fname AS volafname,ps.lname AS volalname
FROM person AS ps
INNER JOIN ctitle AS ct ON ct.titlecode = ps.prename
INNER JOIN persontype ON persontype.pcucodeperson = ps.pcucodeperson AND persontype.pid = ps.pid
INNER JOIN house ON ps.pcucodeperson = house.pcucode AND ps.hcode = house.hcode
WHERE RIGHT(house.villcode,2) <> '00' AND persontype.typecode = 09 AND persontype.dateretire IS NULL
) AS pvola ON pvola.pcucodeperson = house.pcucode AND pvola.pid_vola = house.pidvola
LEFT OUTER JOIN
(
SELECT visitepi.pcucodeperson,visitepi.pid,visitepi.hosservice
FROM visitepi
WHERE visitepi.vaccinecode = 'dTC' AND visitepi.dateepi BETWEEN '2014-10-01' AND '2015-09-30'
) AS vacdTC ON person.pcucodeperson = vacdTC.pcucodeperson AND person.pid = vacdTC.pid
WHERE person.nation = '99' AND person.typelive IN ('0','1','3') AND person.birth BETWEEN '1965-01-01' AND '1995-12-31' AND ISNULL(vacdTC.pid)
ORDER BY village.villno,house.pidvola;