Results 1 to 2 of 2

Thread: Guidance needed to produce report

  1. #1
    ZeroGensis is offline Newbie Net Builder
    Join Date
    Jul 2009
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Guidance needed to produce report

    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
    imported_whitey99's Avatar
    imported_whitey99 is offline Newbie Net Builder
    Join Date
    Jul 2009
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You have to tell the database how the tables you want it to read from are related. The way your query is written, it's going to match every student with every next of kin, and match the Cartesian product with a particular course. You've explicitly limited the query to only one course, but you haven't given any guidance on how to pull only the students, etc, that are relevant for your report.

    This needs a join clause. I'm guessing that there's a many to many relationship between students and courses (a student can take many courses, a course can be taught to many students), and that your next of kin table doesn't map between them. There's probably another table, ideally with a name like students_in_courses, that tells you student #1 is taking course #1 and course #2, whereas student #2 is taking course #38, etc. You'll need this in your query, to limit the Cartesian product you're seeing.
    My directory: MyDirectory FOR SALE
    My sites: Obtain a cash advance | Sound Unsound

Similar Threads

  1. Google: More Guidance on Building High-Quality Sites
    By TopDogger in forum Search Engine News
    Replies: 12
    Last Post: 16 May, 2011, 15:34 PM
  2. You Cannot Produce an Income Online
    By Kovich in forum Business
    Replies: 29
    Last Post: 18 September, 2010, 06:07 AM
  3. [Free] Produce Money by Shortening Links
    By Kovich in forum Marketplace
    Replies: 2
    Last Post: 26 August, 2009, 16:04 PM
  4. Free Report: IR Firm Report
    By DotComBum in forum eBooks
    Replies: 0
    Last Post: 22 June, 2009, 15:11 PM
  5. Report: The 40K Flipping Report
    By DotComBum in forum eBooks
    Replies: 1
    Last Post: 19 June, 2009, 11:39 AM

Tags for this Thread

Bookmarks

Posting Permissions

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