Sql multiple condition in two tables returning wrong data-CodePudding

I have two MySql tables with following schema

Courses(CNO:CHAR(10),CName:VARCHAR(50), DeptId:CHAR(3)): Each course is given a
unique ID called CNO
Register(RNO:CHAR(10), CNO:CHAR(10), Year:INTEGER, Sem:INTEGER, Grade:CHAR)

Now I have to find all (RNO, Year, Sem) triples such that a student with Roll No. RNO in the semester defined by Year, Sem has taken/registered for at least three or more distinct courses, each offered by a different department.

My code is following

With tmp AS(
    SELECT Register.RNO,Register.CNO,Register.Year,Register.Sem,courses.DeptId, ROW_NUMBER() 
    OVER (PARTITION BY DeptId) 
    AS rn 
    from Register 
    inner join courses 
    on courses.CNO=Register.CNO
)
select RNO, Year, Sem
from tmp
where rn=1
GROUP BY RNO,Year,Sem 
having COUNT(CNO)>=3;

(I am using Row_Number to get apply Distinct keyword on one of the column) However this code is failing in the case where student is registered in more than three courses in two different semester and output is comming out to be 1 row instead of two row. Why it is happening and how to resolve it

Here is the sql code for sample data and schema

    CREATE TABLE courses (
    CNO CHAR(10),
    CName VARCHAR(50),
    DeptId CHAR(3),
    PRIMARY KEY (CNO)
);



INSERT INTO courses (CNO,CName,DeptId)
VALUES ("CS1","database","CSE");
INSERT INTO courses (CNO,CName,DeptId)
VALUES ("CS2","deep","CSE");
INSERT INTO courses (CNO,CName,DeptId)
VALUES ("CS3","prob","CSE");
INSERT INTO courses (CNO,CName,DeptId)
VALUES ("MBA4","consume","IME");
INSERT INTO courses (CNO,CName,DeptId)
VALUES ("MBA5","Model","IME");
INSERT INTO courses (CNO,CName,DeptId)
VALUES ("MBA7","consume","IME");
INSERT INTO courses (CNO,CName,DeptId)
VALUES ("MBA8","Model","IME");
INSERT INTO courses (CNO,CName,DeptId)
VALUES ("SOC6","Social","HSS");
INSERT INTO courses (CNO,CName,DeptId)
VALUES ("MBA10","consume","IME");
INSERT INTO courses (CNO,CName,DeptId)
VALUES ("MBA11","Model","IME");
INSERT INTO courses (CNO,CName,DeptId)
VALUES ("SOC12","Social","HSS");
INSERT INTO courses (CNO,CName,DeptId)
VALUES ("SOC9","Social","HSS");

CREATE TABLE Register (
    RNO CHAR(10),
    CNO CHAR(10), 
    Year INTEGER, 
    Sem INTEGER,
    Grade CHAR,
    FOREIGN KEY (CNO) REFERENCES courses(CNO)
);    

INSERT INTO Register (RNO,CNO,Year,Sem,Grade)
VALUES ("1","CS1",1,1,"B");
INSERT INTO Register (RNO,CNO,Year,Sem,Grade)
VALUES ("1","CS2",1,1,"A");
INSERT INTO Register (RNO,CNO,Year,Sem,Grade)
VALUES ("1","CS3",1,1,"B");
INSERT INTO Register (RNO,CNO,Year,Sem,Grade)
VALUES ("1","MBA4",1,1,"B");
INSERT INTO Register (RNO,CNO,Year,Sem,Grade)
VALUES ("1","MBA5",1,1,"A");
INSERT INTO Register (RNO,CNO,Year,Sem,Grade)
VALUES ("1","SOC6",1,1,"A");

INSERT INTO Register (RNO,CNO,Year,Sem,Grade)
VALUES ("1","MBA7",2,2,"B");
INSERT INTO Register (RNO,CNO,Year,Sem,Grade)
VALUES ("1","MBA8",2,2,"A");
INSERT INTO Register (RNO,CNO,Year,Sem,Grade)
VALUES ("1","SOC9",2,2,"A");

INSERT INTO Register (RNO,CNO,Year,Sem,Grade)
VALUES ("1","MBA10",3,3,"B");
INSERT INTO Register (RNO,CNO,Year,Sem,Grade)
VALUES ("1","MBA11",4,3,"A");
INSERT INTO Register (RNO,CNO,Year,Sem,Grade)
VALUES ("1","SOC12",5,3,"A");

Here is a link to a DB Fiddle.

CodePudding user response：

If I've got it right

select Register.RNO, Register.Year, Register.Sem 
from Register 
inner join courses on courses.CNO=Register.CNO
group by RNO,Year,Sem 
having COUNT(distinct courses.CNO)>=3 and COUNT(distinct courses.DeptId) >=3;