I have used subqueries to make this work and here it is:
SELECT St.RegNo as StudentRegNo,
St.Fname as StudentFname,
St.Lname as StudentLname,
Ins.instructorRegNo as InstructorRegNo,
Ins.Fname as InstructorFname,
Ins.Lname as InstructorLname,
St.moduleID as moduleID,
St.courseID as courseID,
St.courseName as courseName
FROM (SELECT distinct T.instructorRegNo,
FROM tblTrainingStudents T, tblSHPEmployee E
WHERE T.instructorRegNo = reg_no) Ins
FROM tblTrainingStudents T, tblSHPEmployee E, tblCourses C
WHERE T.RegNo = reg_no
AND C.moduleID = T.moduleID
AND C.courseID = T.courseID) St
ON Ins.reg_no = St.instructorRegNo
AND St.reg_no = Ins.regNo
I'd like to design this report in Crystal Report and not sure how to get it to work. I can only display either the student Name (First and Lname) or instructor Name (F and L name) but not both by having the employee.Reg_No = trainingStudents.StudentRegNo or employee.Reg_No = trainingStudents.instructorRegNo.
Can anyone help me with this. I have thought about subreport but don't think it works here. Thanks.
You can include the Employee table twice (in SQL query OR in the Database Expert). Assuming you choose to do this through the Database Expert, when you select the Employee table for the second time it will ask you if you want to add this an an alias, and will probably call it Employee_1. Now you can link employee.Reg_No = trainingStudents.StudentRegNo and employee_1.Reg_No = trainingStudents.instructorRegNo, then report out employee.lname for the student's last name, and employee_1.lname for the instructor's last name.