Results 1 to 1 of 1
  1. #1
    Join Date
    Jan 2005
    Posts
    14

    Question Unanswered: Generalization...need help in sql query

    I have problem in doing the select command:

    i have the following table:-
    1) investigator
    2) professor
    3) student
    4) deptassign
    5) assignment

    business rule:-
    investigator is a supertype for professor and student.

    professor and student table are subtype for investigator.

    professor has many to many relation to department, so there is composite table called deptassign between the professor and department.

    student majors from one department, so there is one to many relation (department to student).

    primary keys:
    investigator: inv_id
    professor: prof_id
    student: stu_id
    * stu_id = prof_id = inv_id
    department: dept_id
    deptassign:dept_id and prof_id

    data in the following table:
    investigator: s1, s2, p1, p2 *i used char data type for my pk.
    student: s1, s2
    professor: p1, p2
    dept id: 1, 2
    deptassign: p1 1, p1 2, p2 2

    problem:

    Let say i want to list the ids of professor and student and their respective departments.

    select a.inv_id, e.dept_id
    from investigator a, student b , professor c, deptassign d, department e
    where a.inv_id = b.stu_id and a.inv_id = b.stu_id and b.stu_id = e.dept_id and a.inv_id = a.prof_id and a.prof_id = d.prof_id and d.dept_id = e.dept_id;


    It will gives empty set...
    I think this is because there are overlapped of data ( the one that i bold in the sql query) and it causes the data to cancel out.

    Please advise.

    Thank you very much.
    Last edited by roustabout; 01-18-05 at 04:39.

Posting Permissions

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