SELECT 
	cp.provname
	,cd.distname
	,cs.subdistname	
	,v.villname
	,h.villcode
	,p.hnomoi
	,ct.titlename
	,p.fname
	,p.lname
	,p.hosmain
	,p.hossub
	,cr.rightcode
	,cr.rightname
	,p.officework
	,p.telephoneperson
FROM  person p 
			INNER JOIN house h 
				ON p.hcode=h.hcode 
				AND p.pcucodeperson=h.pcucode 
			INNER JOIN village v 
				ON v.villcode=h.villcode 
			INNER JOIN cprovince cp 
				ON cp.provcode=p.provcodemoi
			INNER JOIN cdistrict cd 
				ON cd.distcode = p.distcodemoi
				AND cd.provcode = cp.provcode
			INNER JOIN csubdistrict cs 
				ON cs.subdistcode = p.subdistcodemoi
				AND cs.distcode = cd.distcode 
				AND cs.provcode = cd.provcode
				AND cs.provcode = cp.provcode
			LEFT JOIN cright cr 
				ON cr.rightcode = p.rightcode
			LEFT JOIN ctitle ct 
				ON ct.titlecode = p.prename
			LEFT JOIN persondeath pd 
				ON pd.pid = p.pid 
				AND p.pcucodeperson = pd.pcucodeperson
WHERE pd.pid IS NULL
			AND p.rightcode = '02'
ORDER BY 
	p.provcodemoi,p.distcodemoi,p.subdistcodemoi
	,h.villcode	
	,CAST(p.hnomoi AS signed);

