Results 1 to 3 of 3

Thread: Oracle SQL Query

  1. #1
    Join Date
    Nov 2010
    Posts
    3

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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;

  3. #3
    Join Date
    Nov 2010
    Posts
    3

    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
  •