CODEWITHSUNDEEP
sqlsqlite
L.S.
L.S.

Reputation: 15

SQL: Finding students who fulfill a course prerequsite

I have these two tables: prerequisite: (cid, pre-cid) (record: sid, cid, qtr , year, grade).

I have to find the students who fulfill the prerequisites for a certain class using only NOT IN. My current query is:

select distinct sid
from record
where sid not in (
        select s.sid
        from record s,
            prerequisite p
        where p.cid = "CSE132X"
            and s.cid <> p.precid
            and s.grade < 2
        );

However, my query returns students who have taken any prerequisite, not all the prerequisites. I'm having trouble figuring out how to make it so that it checks for all the prerequisites of the course.

Upvotes: 0

Views: 794

Answers (2)

azizj
azizj

Reputation: 3777

select distinct r.sid
from record r
where r.sid not in (
    select r.sid
    from prerequisite c
    where c.cid = "CSE132X" and 
    c.pre-cid not in (
          select r2.cid
          from record r2
          where r2.sid = r.sid
    )
);

Upvotes: 1

cshu
cshu

Reputation: 5944

Something like this:

select distinct sid from record y where grade>=2 and not exists( select * from prerequisite where cid='CSE132X' and precid not in (select cid from record x where x.sid=y.sid) );

Upvotes: 0

Related Questions