SQLite Join query returns data that doesn't exist

Question

I am performing a join query on a simple student/teacher db in SQLite. Strangely, the data returned by the following query yields results that were previously not inserted into the tables.

SELECT ss.crsName, ss.stdName, ts.teacherName, ts.term from TeacherSchedule as ts 
inner join StudentSchedule as ss on ss.crsName = ts.crsName 
inner join Teacher as t on t.teacherName = ts.teacherName 
where ss.crsName like 'COMP4900'
order by ss.stdName;

In the image above, you can see for example that student JC is taking COMP4900 from tej and farnaz but the insert statement below never actually specified that JC is taking COMP4900 with farnaz but only with tej.

DROP TABLE Student;
DROP TABLE Course;
drop table Teacher;
drop table StudentSchedule; 
drop table TeacherSchedule;

PRAGMA foreign_keys = on;

CREATE TABLE Student (
  stdID INTEGER PRIMARY KEY  AUTOINCREMENT,
  stdName   TEXT NOT NULL,
  stdAge        INT NOT NULL,
  stdAddress    VARCHAR(50) NOT NULL
);

CREATE TABLE Teacher (
  teacherID INTEGER PRIMARY KEY  AUTOINCREMENT,
  teacherName   TEXT NOT NULL,
  teacherAge        INT NOT NULL,
  teacherAddress    VARCHAR(50) NOT NULL
);

create table Course (
  crsID INTEGER PRIMARY KEY  AUTOINCREMENT,
  crsName VARCHAR(8) NOT NULL,
  crsTerm INT NOT NULL
);

CREATE TABLE StudentSchedule(
  stdSchedule INTEGER PRIMARY KEY  AUTOINCREMENT,
  stdName INT NOT NULL,
  teacherName INT not NULL,
  crsName INT not NULL,
  startTime NUMERIC NOT NULL,
  endTime NUMERIC NOT NULL,
  term INT NOT NULL,
  FOREIGN KEY(stdName) references Student(stdID),
  FOREIGN KEY(teacherName) REFERENCES Teacher(teacherID),
  FOREIGN KEY(crsName) REFERENCES Course(crsID)
);

CREATE TABLE TeacherSchedule(
  teacherSchedule INTEGER PRIMARY KEY  AUTOINCREMENT,
  teacherName INT not NULL,
  crsName INT not NULL,
  startTime NUMERIC NOT NULL,
  endTime NUMERIC NOT NULL,
  term INT NOT NULL,
  FOREIGN KEY(teacherName) REFERENCES Teacher(teacherID),
  FOREIGN KEY(crsName) REFERENCES Course(crsID)
);


insert into Student (stdName, stdAge, stdAddress) values ('John', 25, 'vancouver, bc');
insert into Student (stdName, stdAge, stdAddress) values ('Rost', 25, 'Delta, bc');
insert into Student (stdName, stdAge, stdAddress) values ('Mike', 29, 'vancouver, bc');
insert into Student (stdName, stdAge, stdAddress) values ('Johnny', 27, 'surrey, bc');
insert into Student (stdName, stdAge, stdAddress) values ('Jeff', 27, 'vancouver, bc');
insert into Student (stdName, stdAge, stdAddress) values ('Li', 32, 'vancouver, bc');
insert into Student (stdName, stdAge, stdAddress) values ('JC', 24, 'richmond, bc');

Insert into Teacher(teacherName, teacherAge, teacherAddress) values ('tej', 65, 'surrey');
Insert into Teacher(teacherName, teacherAge, teacherAddress) values ('aman', 70, 'surrey');
Insert into Teacher(teacherName, teacherAge, teacherAddress) values ('chris', 40, 'vancouver');
Insert into Teacher(teacherName, teacherAge, teacherAddress) values ('farnaz', 41, 'delta');
Insert into Teacher(teacherName, teacherAge, teacherAddress) values ('richard', 99, 'richmond');

insert into Course (crsName, crsTerm) values ('COMP1510', 1);
insert into Course (crsName, crsTerm) values ('COMP2510', 2);
insert into Course (crsName, crsTerm) values ('COMP4100', 3);
insert into Course (crsName, crsTerm) values ('COMP4900', 3);
insert into Course (crsName, crsTerm) values ('COMP2546', 2);
insert into Course (crsName, crsTerm) values ('COMP1536', 1);

insert into StudentSchedule(stdName, teacherName, crsName, startTime, endTime, term) values ('Johnny','tej','COMP1510',1200,200,1);
insert into StudentSchedule(stdName, teacherName, crsName, startTime, endTime, term) values ('Mike','farnaz','COMP2526',400,600,3);
insert into StudentSchedule(stdName, teacherName, crsName, startTime, endTime, term) values ('JC','richard','COMP1536',1200,400,1);

insert into StudentSchedule(stdName, teacherName, crsName, startTime, endTime, term) values ('Matt','tej','COMP4900',1000,1200,2);
insert into StudentSchedule(stdName, teacherName, crsName, startTime, endTime, term) values ('Jeff','tej','COMP4900',1000,1200,2);
insert into StudentSchedule(stdName, teacherName, crsName, startTime, endTime, term) values ('JC','tej','COMP4900',1000,1200,2);

insert into StudentSchedule(stdName, teacherName, crsName, startTime, endTime, term) values ('Rost','farnaz','COMP4900',1200,200,3);
insert into StudentSchedule(stdName, teacherName, crsName, startTime, endTime, term) values ('Jeff','farnaz','COMP4900',1200,200,3);
insert into StudentSchedule(stdName, teacherName, crsName, startTime, endTime, term) values ('John','farnaz','COMP4900',1200,200,3);

insert into TeacherSchedule(teacherName, crsName, startTime, endTime, term) values ('tej', 'COMP1510', 1200, 200, 1);
insert into TeacherSchedule(teacherName, crsName, startTime, endTime, term) values ('tej', 'COMP4900', 1000, 200, 2);
insert into TeacherSchedule(teacherName, crsName, startTime, endTime, term) values ('tej', 'COMP4100', 1000, 1200, 1);
insert into TeacherSchedule(teacherName, crsName, startTime, endTime, term) values ('aman', 'COMP2510', 1200, 200, 1);
insert into TeacherSchedule(teacherName, crsName, startTime, endTime, term) values ('aman', 'COMP1510', 300, 400, 1);
insert into TeacherSchedule(teacherName, crsName, startTime, endTime, term) values ('chris', 'COMP4100', 200, 400, 2);
insert into TeacherSchedule(teacherName, crsName, startTime, endTime, term) values ('farnaz', 'COMP2526', 400, 600, 3);
insert into TeacherSchedule(teacherName, crsName, startTime, endTime, term) values ('richard', 'COMP1536', 1200, 400, 1);
insert into TeacherSchedule(teacherName, crsName, startTime, endTime, term) values ('farnaz', 'COMP4900', 1200, 200, 3);

Could someone explain why this is or see a problem?

Answer 1

In your query, you had forgotten to specify that Teacher.teacherName should match StudentSchedule.teacherName. You were joining rows where student and teacher did not match, thus creating the extra rows.

This query will give you the rows you want.

SELECT ss.crsName, ss.stdName, ts.teacherName, ts.term from TeacherSchedule as ts 
inner join StudentSchedule as ss on ss.crsName = ts.crsName
inner join Teacher as t on t.teacherName = ts.teacherName AND t.teacherName = ss.teacherName
where ss.crsName like 'COMP4900'
order by ss.stdName;