SELECT
' ' AS `ลำดับที่`,
person.pid AS HN,
CONCAT(ctitle.titlename,person.fname,' ',person.lname) AS `ชื่อ - สกุล`,
house.hno AS `บ้านเลขที่`,
village.villno AS `หมู่ที่`,
cdisease.diseasenamethai AS `โรค`,
cdrug.drugname AS 'รายการที่ได้รับ'
FROM
person
INNER JOIN ctitle ON ctitle.titlecode = person.prename
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
INNER JOIN visit ON person.pcucodeperson = visit.pcucodeperson AND person.pid = visit.pid
INNER JOIN visitdiag ON visit.pcucode = visitdiag.pcucode AND visit.visitno = visitdiag.visitno
INNER JOIN visitdrugdental ON visitdiag.pcucode = visitdrugdental.pcucode AND visitdiag.visitno = visitdrugdental.visitno
INNER JOIN cdisease ON visitdiag.diagcode = cdisease.diseasecode
INNER JOIN cdrug ON visitdrugdental.dentcode = cdrug.drugcode
WHERE
visit.visitdate BETWEEN '2015-10-01' AND '2016-09-30';