<p>I have two MySql tables with the following schema</p> <pre class="brush:php;toolbar:false;">Courses(CNO:CHAR(10),CName:VARCHAR(50), DeptId:CHAR(3)): Each course has a unique ID called CNO Register(RNO:CHAR(10), CNO:CHAR(10), Year:INTEGER, Sem:INTEGER, Grade:CHAR)</pre> <p>The current task is to find all tuples that satisfy (r1, r2, d), in which the student with student ID r1 has completed all the DeptIds completed by the student with student ID r2 as <strong>ID</ Strong>'s department's courses. </p> <p>My method is to first find all the courses completed in the department by the student with student number r2, and then similarly find all the courses completed in the department by the student with student number r1, and compare whether they are the same. But I don't know if this is correct or how to compare the two results. </p> <p>This is the code I use to check all courses completed by the student with middle school number r2 in a specific department: </p> <p>Here is the link to the code and data DB Fiddle</p>

RNO1 RNO2
1    2
1    3
2    3

RNO1 RNO2 DeptId
1    2    IME
1    2    CHE
1    2    ECO
1    3    IME
1    3    CHE
2    3    ECO

SELECT
   *
FROM
   rno1_rno2_deptid as d
WHERE
   NOT EXISTS((SELECT DISTINCT CNO FROM Register AS r WHERE r.RNO = d.RNO1 AND r.DeptId = d.DeptId) 
   MINUS 
   (SELECT DISTINCT CNO FROM Register AS r WHERE r.RNO = d.RNO2 AND r.DeptId = d.DeptId))