SELECT需要对PostgreSQL中的一对多表进行多次联接 [英] SELECT requiring multiple joins with one to many tables in PostgreSQL

问题描述

玩具示例：此EDR图中显示了一个数据库模式：

Toy example: I have a database schema shown in this EDR diagram:

• Student 与 StudyGroup一对多


• StudyGroup 与借用
的一对多

• 借书与书一对一。

• Student one-to-many with StudyGroup
• StudyGroup one-to-many with Borrowed
• Borrowed one-to-many with Books.

我想获得单个 的所有研究组 借用的所有 本书学生。

I want to get all books that have been borrowed by all study groups of a single Student.

+------------+---------+----------------+
| student.id | book.id | study_group.id |
+------------+---------+----------------+
|          1 |       1 |              1 |
|          1 |       2 |              1 |
|          1 |       3 |              1 |
|          1 |       4 |              2 |
|          1 |       1 |              2 |
+------------+---------+----------------+

在这种情况下，我不确定如何构造多个联接

I'm unsure how to construct the multiple joins in this case,

SELECT student.id, book.id, study_group.id
FROM ...
    INNER JOIN...
    INNER JOIN...
WHERE student.id == 1

推荐答案

我建议您阅读 SQL JOIN和不同类型的JOIN

如果您想要显示表格，

SELECT student.id, book.id, study_group.id
FROM student
    INNER JOIN study_group on (student.id = study_group.student_id)
    INNER JOIN borrowed on (study_group.id = borrowed.group_id)
    INNER JOIN book on (borrowed.book_id = book.id)
WHERE student.id == 1

这篇关于SELECT需要对PostgreSQL中的一对多表进行多次联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！