Left Join

[rekhatr10]

Hi everyone,

CR 7.0
SQL 2000
I have 2 table appt & docdesc link on account number and adate to svvdate left outer join link. In docdesc you have desction as 'patient letter', 'Patient notes', prescriptions, E& M Family Med. I want to display all the results from appts table whether there is a records or not in the docdesc table and I want only records with E& M Family Med. Right now if Prescritoon is in docdesc table the record is suppressed I want it displayed becuase it does not have E&M FamilyMed. I don't want to display records from patientletter,prescriptions. How can I get this accomplished.
I am stuck Please help
Thank you for your advice.

Rekha

 

[MrBillSC]

Rekha,
As I understand it, if you have a left out join from table A to table B and a select statement on table B, the left outer join becomes an equal join. One way around this problem is to remove the select statement from table B (docdesc in your example). Group by Patient with the patient information in the Patient group header. Place the docdesc info in the detail section. Conditionally supress the detail section if desction <> "E& M Family Med". Also check the Suppress Blank Section box for patients that have no docdesc records.

Unfortunately this will cause the report to select all of the docdesc records rather than just the "E& M Family Med" records. The only other approach would be to use a subfile. If anyone knows how to do a select on a left out join, I would love to know about it.

MrBill