I am quite new to SQL and have a question I hope someone can give me a hand
with. I am building a SQL statement on the fly depending on which person
(Rad_no field which is reflected in the first page of a PageControl object
'PatientGrid').

The following statement worked almost like I wanted it to, except it would
drop any exam (from examhist file) that did not have a corresponding
department and proc_code in the itemmast file. I understand that an inner join
will do this (drop unmatched records), so I need to use an outer join. But I
have not been able to get the syntax correct.

EpisodeLookUpQuery.SQL.Add('SELECT pat_examhist.*, itemmast.im_long_desc
FROM pat_examhist, itemmast
WHERE pat_examhist.pe_pt_rad_no = '+PatientGrid.Fields[0].AsString+' AND
(itemmast.im_dept=pat_examhist.pe_dept AND
itemmast.im_proc_code=pat_examhist.pe_proc_code)
ORDER BY pe_examdate DESC') ;

The idea is to find the description in the itemmast file that corresponds to
the pe_dept AND proc_code from the examhist file, and bring it into the
displayed dataset for the user - yet still retaining any data from the
examhist that doesn't have a match for the pe_dept AND proc_code for the user.

I am sending this query to  MSSQL server 6.5 (I believe that is the correct
version). The only examples I have are from the book "SQL for Dummies", I
guess I rate lower than that right now on the learning curve :-) .

TIA for your help.

-Ray-

Try something like this:

EpisodeLookUpQuery.SQL.Add('SELECT pat_examhist.*, itemmast.im_long_desc
FROM pat_examhist
Left Outer Join itemmast
on (itemmast.im_dept=pat_examhist.pe_dept AND
itemmast.im_proc_code=pat_examhist.pe_proc_code)
WHERE pat_examhist.pe_pt_rad_no = "'+PatientGrid.Fields[0].AsString + '"
ORDER BY pe_examdate DESC') ;

Raymond M. Summerlin Sr. <summerli...@msx.upmc.edu> wrote in message
news:77isu6$sm25@forums.borland.com...