SELECT v.pid AS 'HN',
p.idcard AS 'เลขบัตรประชาชน',
concat(t.titlename,p.fname," ",p.lname) as 'ชื่อ-สกุล',
v.visitdate AS 'วันที่มารับบริการ',
v.rightcode AS 'สิทธิบัตร',
v.rightno AS 'หมายเลขบัตร',
v.symptoms AS 'อาการ',
GROUP_CONCAT(d.diagcode) AS 'วินิจฉัยโรค'
FROM visit AS v
LEFT OUTER JOIN person AS p ON p.pid = v.pid
LEFT OUTER JOIN ctitle AS t ON t.titlecode = p.prename
LEFT OUTER JOIN visitdiag AS d ON d.visitno = v.visitno
LEFT OUTER JOIN cright AS r ON v.rightcode = r.rightcode
WHERE v.visitdate BETWEEN "2014-11-01" AND "2014-11-30" AND
v.hosmain = "10671" and r.mapright="4200"
GROUP BY v.pid
ORDER BY v.visitdate;