SELECT p.hn,concat(p.pname,p.fname,' ',p.lname) as patientname,p.cid,concat(p.firstday,' ',p.reg_time) as regis_date,
CONCAT(n1.nationality,'-',n1.name) as nationality,CONCAT(n2.nationality,'-',n2.name) as citizenship,o.name as employee from patient p
LEFT OUTER JOIN ksklog k ON k.detail=p.hn AND k.modifytype = 'Insert'
LEFT OUTER JOIN opduser o ON o.loginname=k.loginname
LEFT OUTER JOIN nationality n1 ON n1.nationality=p.nationality
LEFT OUTER JOIN nationality n2 ON n2.nationality=p.citizenship
WHERE p.firstday BETWEEN '$startdate' AND '$enddate' and p.nationality = '00' OR p.citizenship = '00'
ORDER BY p.hn;
2) คอลัมน์ firstday ข้อมูลเป็นแบบไหนครับ DATE หรือ DATETIME
- DATE : WHERE (p.firstday BETWEEN '2010-01-30' AND '2010-09-29')
- DATETIME : WHERE (p.firstday BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')
หรือ
- DATE : WHERE (p.firstday >= '2010-01-30' AND datetime <= '2010-09-29');
- DATETIME : WHERE (p.firstday >= '2010-01-30 14:15:55' AND datetime <= '2010-09-29 10:15:55');
หรือถ้า p.firstday เป็น DATETIME จะเอาแค่ DATE ก็ใช้ฟังก์ชั่นช่วย
- WHERE DATE(p.firstday) BETWEEN '2016-12-01' AND '2016-12-10';
Load balance : Server 01
