Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    33

    Question Unanswered: tables connections

    I have 3 tables and they are defined as below:

    Employee table
    Reg_No,
    Fname,
    Lname

    TrainingStudents table

    studentRegNo,
    instructorRegNo
    moduleID,
    courseID

    Courses table
    moduleID,
    courseID,
    courseName

    I'd like to display the following :

    studentRegNo, student Lname, student Fname, instructorRegNo, instructor Fname, instructor Lname, and courseName.

    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,
    E.reg_no,
    E.Fname,
    E.Lname,
    T.regNo
    FROM tblTrainingStudents T, tblSHPEmployee E
    WHERE T.instructorRegNo = reg_no) Ins

    INNER JOIN

    (SELECT T.instructorRegNo,
    T.regNo,
    E.reg_no,
    E.Fname,
    E.Lname,
    C.CourseID,
    C.moduleID,
    C.courseName
    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.

  2. #2
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112

    Try using a table alias

    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.

  3. #3
    Join Date
    Apr 2004
    Posts
    33
    I got it work. Thanks very much for your help. -Thien

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •