-
Oracle SQL Query
I have the following scenario.Two tables transcript : A student transcript that shows what classes
each student took. Prereq what is the prerequisite for each class.
Transcript
STUDID | CLASSID
----------------------------
STID1 | A
STID1 | B
STID1 | C
STID2 | A
Prereq
CLASSID | PREID
--------------------------
D | A
D | B
D | C
Assume that student STID1 and STID2 are enrolled in class D. I need to write a sql query that will return the student id SID of only the student that satisfied all the prerequisites. That would be student STID1. If I join the two tables with "exists" or "in" I get both students back. I can possibly write a cursor to do this, but I need a single sql query.
Help will be appreciated.
-
You can use left outer join prereq with transcript table, if any prereq is missing then you get null in the result set, just filter that out and you have the student.
select tr.stdid
from prereq as pr
left outer join trascript as tr
on pr.classid = tr.classid
where pr.classid='D'
and tr.classid is not null;
-
Thanks This helps
I tried on my local Oracle installation.
Thanks for you response
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|