我用java编写了这个项目。我希望这个东西在 1 个显示器中显示来自不同表的数据。
这是java代码
static void showPersonality() {
try {
Statement stmt1 = conn.createStatement();
ResultSet rs1 = stmt1.executeQuery("SELECT * FROM faculty JOIN teacher ON faculty.id=teacher.faculty_id JOIN department ON faculty.id=department.faculty_id JOIN course ON department.id=course.department_id");
while (rs1.next()) {
String namaFakultas = rs1.getString("faculty_name");
String namaDosen = rs1.getString("teacher_name");
String namaDepartemen = rs1.getString("department_name");
String namaKursus = rs1.getString("course_name");
System.out.println(String.format("| %s | %s | %s | %s |", namaFakultas, namaDepartemen, namaKursus, namaDosen));
}
} catch (Exception e) {
e.printStackTrace();
}
}
我还做了4张桌子,分别是教师,部门,老师和课程
教师表(我插入了 1 个数据)
CREATE TABLE faculty(
id INT NOT NULL AUTO_INCREMENT,
faculty_name VARCHAR(100) NOT NULL,
PRIMARY KEY(id)
);
部门表(我在 2 个学院中插入了 1 个数据)
CREATE TABLE `department` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`faculty_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `faculty_id` (`faculty_id`),
CONSTRAINT `department_ibfk_1` FOREIGN KEY (`faculty_id`) REFERENCES `faculty` (`id`)
);
教师表(我在 2 个教师中插入了 1 个数据,每个数据都有不同的部门)
CREATE TABLE `teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`teacher_name` varchar(255) NOT NULL,
`faculty_id` int(11) NOT NULL,
`department_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `department_id` (`department_id`),
CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`),
KEY `faculty_id` (`faculty_id`),
CONSTRAINT `teacher_ibfk_2` FOREIGN KEY (`faculty_id`) REFERENCES `faculty` (`id`)
);
课程表(我插入了2个数据,每个数据都有不同的部门)
CREATE TABLE `course` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`course_name` varchar(255) NOT NULL,
`department_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `department_id` (`department_id`),
CONSTRAINT `course_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`)
);
执行代码后,它显示 4 个数据
| Teknik | Informatika | OOP | Adi Gunawan |
| Teknik | Sistem Informasi | Basis Data | Adi Gunawan |
| Teknik | Informatika | OOP | Ani Wijaya |
| Teknik | Sistem Informasi | Basis Data | Ani Wijaya |
我希望它只显示 2 个数据,其中包含除教师表之外的所有不同表
| Teknik | Informatika | OOP | Adi Gunawan |
| Teknik | Sistem Informasi | Basis Data | Ani Wijaya |
我的java 代码或我的数据库有什么问题吗?
(输出只是显示名称列表,而不是id.您应该显示所有原始数据列表以获得正确答案)
(猜测其他 3 个表的名称值是唯一的,但课程表的名称不是唯一的。
我认为这只是数据问题。
在查询中,在执行前 3 个连接后,可能会返回 2 行。
| Teknik | Informatika | OOP
| Teknik | Sistem Informasi | Basis Data
并查看下一个第 4 个连接步骤(重新排序输出)后的最终结果
| Teknik | Informatika | OOP | Adi Gunawan |
| Teknik | Informatika | OOP | Ani Wijaya |
| Teknik | Sistem Informasi | Basis Data | Adi Gunawan |
| Teknik | Sistem Informasi | Basis Data | Ani Wijaya |
如您所见, 这意味着每个OOP
和Basis Data
都连接了 2 行course table
因此,原始数据期望:
# rows in course table
... OOP_id | Adi Gunawan
OOP_id | Ani Wijaya
Basis Data_id | Adi Gunawan
Basis Data_id | Ani Wijaya
如果需要帮助,
| Teknik | Informatika | OOP | Adi Gunawan |
| Teknik | Sistem Informasi | Basis Data | Ani Wijaya |
on
第 4 个连接的子句,也许不是department_id
列,它必须是另一列
如果course data
有faculty_id
,则与两个表faculty_id
连接
编辑。
在您的评论之后, 我发现我错过了一件事。
teacher
表有两个关系
- 与
faculty
表的多对一关系。 - 与
department
表的多对一关系。
所以 在加入teacher
后的on
子句中,你应该添加条件,teacher
的department_id
=department
的id
。不仅仅是faculty.id=teacher.faculty_id
查询应该像
SELECT *
FROM faculty
JOIN department ON faculty.id=department.faculty_id
JOIN teacher ON faculty.id=teacher.faculty_id AND teacher.department_id = department.id
JOIN course ON department.id=course.department_id