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
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.