SELECT
"Thai" AS eclinic ,
"Medicine" AS admitdepart,
i.*,
pcn.cardno AS CID_CODE,
concat(pt.pname,pt.fname," ",pt.lname) AS PT_NAME,
concat(pt.addrpart," หมู่ ",pt.moopart," ต.",t3.name," อ.",t2.name," จ.",t1.name) AS ADDRESS,
sx.name AS SEX_NAME,
mr.name AS MARRYSTATUS,
oc.name AS OCCUPATION,
pt.birthday AS DOB,
pt.informname AS INFORMNAME,
concat("<",pt.informrelation,">") AS INFORMRELATION,
pt.informaddr AS INFORMADDR,
pty.name AS PTTYPENAME,
wd.name AS WARD_NAME,
sp.name AS SPCLTY_NAME
FROM an_stat i
LEFT OUTER JOIN ptcardno pcn ON pcn.hn=i.hn AND pcn.cardtype="01"
LEFT OUTER JOIN patient pt ON pt.hn=i.hn
LEFT OUTER JOIN sex sx ON sx.code=pt.sex
LEFT OUTER JOIN marrystatus mr ON mr.code=pt.marrystatus
LEFT OUTER JOIN occupation oc ON oc.occupation=pt.occupation
LEFT OUTER JOIN pttype pty ON pty.pttype=i.pttype
LEFT OUTER JOIN ward wd ON wd.ward=i.ward
LEFT OUTER JOIN spclty sp ON sp.spclty=i.spclty
LEFT OUTER JOIN thaiaddress t1 ON t1.codetype="1" AND t1.chwpart=pt.chwpart
LEFT OUTER JOIN thaiaddress t2 ON t2.codetype="2" AND t2.chwpart=pt.chwpart AND t2.amppart=pt.amppart
LEFT OUTER JOIN thaiaddress t3 ON t3.codetype="3" AND t3.chwpart=pt.chwpart AND t3.amppart=pt.amppart
AND t3.tmbpart=pt.tmbpart
WHERE i.an="460101000"
LIMIT 1