mysql选择查询从多个表返回重复的结果

If this question is a little vague just let me know and I will provide more info.

如果这个问题有点模糊，请告诉我，我会提供更多的信息。

I have written a query that gets data from multiple tables but it isn't working how I expected it too and I am completely stumped.

我编写了一个查询，该查询从多个表中获取数据，但它不像我预期的那样工作，我完全被难住了。

Here is my code:

这是我的代码:

SELECT students.student_fname, students.student_lname
FROM  students, enrolments
WHERE enrolments.courseID = 'C001';

but this just returns all of the students first and last names in the students table and these names are displayed twice.

但它只返回学生表中所有学生的姓和名，这些名字会显示两次。

Here is the code for the two tables:

以下是两个表格的代码:

CREATE TABLE students
(
studentID CHAR(10) NOT NULL,
student_fname VARCHAR(15) NOT NULL,
student_lname VARCHAR(15) NOT NULL,
DOB VARCHAR(10) NOT NULL,
CONSTRAINT pk_students PRIMARY KEY (studentID)
);

CREATE TABLE enrolments
(
enrolmentNo int NOT NULL AUTO_INCREMENT,
studentID CHAR(10) NOT NULL,
courseID CHAR(4) NOT NULL,
CONSTRAINT pk_enrolments PRIMARY KEY (enrolmentno),
FOREIGN KEY (studentID) REFERENCES students (studentID),
FOREIGN KEY (courseID) REFERENCES courses (courseID)
)ENGINE = INNODB;

2 个解决方案

#1

This is because you've not defined how students relate to enrolments.

这是因为你没有定义学生与注册学生的关系。

You either need to use an inner join or add a where clause that show how they relate.

您需要使用一个内部连接，或者添加一个where子句来显示它们之间的关系。

For example:

例如:

FROM Students
INNER JOIN enrolments on Students.ID = enrolments.studentID

或

FROM students, enrolements 
WHERE enrolments.studentID = students.ID

The first method is newer and preferred by many; but the legacy method is also supported.

第一种方法是较新的并且被许多人所喜欢;但是也支持遗留方法。

To gain a better understanding of table joins take a look at this most excellent article: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

要更好地理解表连接，请阅读这篇最优秀的文章:http://www.codinghorror.com/blog/2007/10/a- visualexplain -sql-joins.html

Otherwise you get what is called a Cartesian product of the two tables all data relates to all data.

否则你会得到所谓的两个表的笛卡尔积所有的数据都与所有的数据相关。

If you do not have a unique index on enrolements (a student can only have 1 class enrolment) then a select Distinct field names or where... group by fields will also limit your results to close to what you're looking for as well.

如果您对enro没有一个唯一的索引(一个学生只能有一个班级注册)，那么选择一个不同的字段名或者在哪里……按字段分组也会限制你的搜索结果接近你想要的。

============================ in response to follow-up comment===================

= = = = = = = = = = = = = = = = = = = = = = = = = = = =为了应对后续评论= = = = = = = = = = = = = = = = = = =

SELECT S.student_fname, S.student_lname 
FROM students S 
INNER JOIN enrolments  E
  ON S.StudentID = E.StudentID
WHERE e.courseID = 'C001'
GROUP BY S.Student_Fname, S.Student_lname
ORDER BY S.Student_LName, S.Student_FName

The group by eliminates duplicates for the same course. The "ON statement tells the database how students relates to enrolments. The order by is just to provide a reasonable order to results.

小组通过消除重复为同一课程。“ON”语句告诉数据库学生与注册者的关系。这个订单只是为了给结果提供一个合理的订单。

#2

You cannot fetch data from two tables in this way. You have to join tables.

您不能以这种方式从两个表中获取数据。你必须加入表格。

#1