Search code examples
sqldatabasepostgresqljoinrelational-division

SELECT requiring multiple joins with one to many tables in PostgreSQL

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

EDR diagram

  • 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
Solution

  • I recommend you to read SQL JOIN and different types of JOINs

    If you want your table as shown:

    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