Sql multiple conditions in two tables return wrong data
I have two MySql tables with the 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 so that the student has roll number RNO in the semester As defined by year, Sem has taken/enrolled in at least three or more different courses, each offered by a different department.
My code is as follows
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 used the Row_Number to apply the Distinct keyword on one of the columns) However, this code will fail if the student is enrolled in more than three courses in two different semesters and the output is 1 row instead of 2 rows. Why does this happen and how to fix it
This is the SQL code for the 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 the link to DB Fiddle.
