sql - Rewriting a query in SQLite

Question

Schema:

student: sid
course: cid
prerequisite: cid, pre_cid
record: sid, cid

I wanted to list all students who have taken all the prerequisites for PSY100. I only need to display sid (student id).

The records table may have multiple entries from the same sid if that student has taken multiple classes (cid).

I have the following query, however I wanna know if I can rewrite this without the use of DISTINCT and still only using NOT EXIST.

SELECT DISTINCT r.sid
FROM record r
WHERE NOT EXISTS (
  SELECT * 
  FROM prerequisite p
  WHERE p.cid = 'PSY100' 
  AND NOT EXISTS (
    SELECT * 
    FROM record r2
    WHERE r2.sid=r.sid AND r2.cid=p.pre_cid
)
);

Thank you!

question from:https://stackoverflow.com/questions/65948150/rewriting-a-query-in-sqlite

Answer 1

Waitting for answers