Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2008
    Posts
    2

    Question Unanswered: problems with report using isql*plus

    Hi there,

    As the title of this topic illustrates, i'm having trouble dumping relevant data from columns of different tables. Using isql*plus, I have three tables appropriatly related. A 'course' table, 'student' and 'next_of_kin' tables. I have many students enrolled on various courses but only a hanfull of courses offer the module option 'Database Systems'. I have no 'module' table but i know the three course names which provide the module option. I intend on producing a report hich lists all students enrolled on the courses which provid the module option 'Database Systems'. I have attempted the report but i keep getting a 'cartesian product' displaying all next_of_kin names instead of the appropriate. Also i am struggling to come up with the right WHERE statement to depict only the three courses which provide the module option 'Database Systems' as defined by 'courseNo' in both 'course' and 'student' tables.

    Here is the most recent attempt:

    --set echo off
    --set pagesize 24
    --set feedback off
    --set linesize 78

    col A format 99999999 heading 'Student No'
    col B format A15 heading 'Student Name'
    col C format A15 heading 'Course Name'
    col D format 99999999 'Course No'
    col E format A10 heading 'Next-of-Kin'
    break on A skip 1 on B

    TTitle 'Business Studies 1 Option BT300'
    BTitle 'Prepared By : Richard Stroud / 20527796'

    Select student.StudentNo "Student No",
    student.fName || ' ' || student.lName "Student Name",
    course.title "Course Name",
    course.courseNo "Course No",
    next_of_kin.fName || ' ' || next_of_kin.lName "Next-of-Kin"

    From student, course, next_of_kin
    where course.courseNo = '12885545'

    order by studentNo

    --clear columns
    --TTitle off
    --BTitle off
    --set feedback on
    --set pagesize 24
    --clear breaks


    Any help towards this toipic will be GREATLY appreciated

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Since iSQL *Plus is used with Oracle, that's where I'm moving this thread.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You have to join the three tables in your where clause (or from using ANSI notation) to avoid the Cartesian join.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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