Crystal Reports SQL command issue

Message

     Hi All, 

I have a report that consists of two parts: one part is focused on students who have overdued item loans from the library; the other part is the staff who runs the report with some staff related information.

The first part is a normal record selection, with the second part I use first SQL command to retrieve staff who runs the report; I created another SQL command to retrieve staff who has the authority. Both commands are actually same (they know who is a general staff, who is the authoriser).

I also created a view on the SQL DB server which populated all library staff (not linked to any table, nor do the commands), and passing the selected staff as parameter to the command

Both commands are inner joined with tables. The problem is when one of the table (stores staff emails) has NULL value, (no link to other table), the report is crashed. On the SQL server I use the same SQL and found that if one of staff has no email, the SQL still runs, but this staff is not retrieved.

OK, if I changed to left outer join, all staff with or without email will be retrieved from SQL server.  So I changed the left outer join in SQL command in Crystal Reports XI, and when running, I found the report is in loop, never ends! 

With the previous command (inner join one), if I selected a staff who happens to have email, the report runs OK  with students with overdue loans in the report as well as staff details.

My command looks liek below:
SELECT TEXT as EMAIL, NUMBER, HEAD, DEPT, POSN,ALL_INSTITUTIONS.TXT AS CAMPUS FROM BDE 
INNER JOIN UDT ON BDE.IRN = UDT.IRN 
INNER JOIN HEADING ON UDT.IRN = HEADING.IRN
INNER JOIN BDT ON UDT.IRN = BDT.IRN
INNER JOIN  INS ON INS.IRN = UDT.BRANCH
INNER JOIN ALL_INSTITUTIONS ON ALL_INSTITUTIONS.CODE = INS.CODE

BDE is the table which stores email,
TEXT is email address,
NUMBER is telephone number, HEAD is the staff name, DEPT is department,
POSN is staff position, Campus of course.
These tables should not be linked to the student tables in this design, as  a staff may run this report against several campuses. 

The report has group on student names with their overdue loans in the detail secion. The staff details are in the group footer. My customer insists they (student and staff )both have to appear on the same page.
the report looks like a form which things like explanation, signature, etc.
It's not necessary that a staff has to have email address in a sense.

could anyone please advise on the above problem? Thanks in advance.

John

Edited by johnwsun - 04 Nov 2009 at 8:34pm