Hello, all.
Having a small problem with MySQL and need some expert help. Trying to do a left
outer join on one table out of seven. As shown below the only table which may have
no row would be the pnotes table. Also shown below is the results I am getting (*use
a fixed font for proper viewing*). There should be two rows returned, the complete
one as shown below and one with partial data, i.e., missing the data stored in column
pnotes_A which contains several fields separated by colons (columns Alice, Disk#,
RTech, STech, Scored, Faxed in the report sample below).
Any help would be greatly appreciated.
select concat(
caldateof,
',',ptlname,
',',ptfname,
',',roomname,
',',cptcode,
',',ptrefdoc,
',',ptphy1,
',',pnotes_A
)
from scheduler sch,
patient pat,
procrec,
cpt,
physician phy,
room
left outer join pnotes on pat.id = pnotes.pnotespat
and pnotes.pnotesdescrip = 'Tech'
where MONTH(sch.CALDATEOF) = $xmonth
and pat.ptrefdoc = phy.id
and pat.id = procrec.procpatient
and procrec.proccpt = cpt.id
and sch.calpatient = pat.id
and sch.calroom = room.id
order by caldateof, roomdescrip
bash script parses output of above script to produce below report:
Tech Score Recap - May Fri May 18
20:04:38 EDT 2001
Test Date Patient Name Alice Dsk Lab Type Ref Phy Int Phy RTech STech
Scored Faxed
NULL NULL, NULL NULL NULL NULL NONE NULL NULL NULL NULL
NULL NULL, NULL NULL NULL NULL NONE NULL NULL NULL NULL
2001-05-14 Doe, John 311 P52 MCP1 PSG Arthur Jordan KS
NULL NULL, NULL NULL NULL NULL NONE NULL NULL NULL NULL
Total Patients: 4
