mysql - mysql如何连接表

Question

我的 mysql 数据库中有 3 个表:student、progress 和 subject。当我尝试选择数据时:

SELECT progress.id, progress.mark, subject.pname FROM progress, subject WHERE   progress.id_student = 1;

我得到下表:

id  mark  pname
1   5     Math
1   5     Physics

表格进度我只有一项:

id  mark   id_student   id_subject
1   5      1            1

如何通过 student_id 获取学生进度？

CREATE DATABASE students;
USE students;
CREATE TABLE student (
id int NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL,
address varchar(60) NOT NULL,
byear int NOT NULL,
eyear int NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE subject (
id int NOT NULL AUTO_INCREMENT,
pname varchar(20) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE progress (
id int NOT NULL AUTO_INCREMENT,
mark int NOT NULL,
id_student int NOT NULL,
id_subject int NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (id) REFERENCES student (id),
FOREIGN KEY (id) REFERENCES subject (id)
);

Answer 1

SELECT progress.id, progress.mark, subject.pname 
FROM progress LEFT OUTER JOIN subject ON (progress.id_subject= subject.id)
WHERE   progress.id_student = 1;