SELECT requiring multiple joins with one to many tables in PostgreSQL

Question

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

Please show what parts you can do. [ask] [help] [Re homework questions.](https://meta.stackoverflow.com/q/334822/3404097) Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table — philipxy, May 24 '20 at 10:10
Ask 1 specific researched non-duplicate question the first place you are stuck. But clearly we can expect it to be a beginner's faq. Also your overall query assignment is a faq--although your phrasing here is unclear. What does "of a single student" mean? Before considering posting please read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. — philipxy, May 24 '20 at 10:11
Hint: if your goal is `I want to get all books` , then it seems illogical to start your query with `SELECT student.id, ...` — wildplasser, May 24 '20 at 10:12
This invovles relational division. (wildplasser also added that tag for you.) This will most likely have been discussed in your textbook. [check if a column contains ALL the values of another column - Mysql](https://stackoverflow.com/q/28939367/3404097) [Select values that meet different conditions on different rows?](https://stackoverflow.com/q/477006/3404097) PS Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Include just what is needed. Use images only for what cannot be expressed as text or to augment text. — philipxy, May 24 '20 at 10:20
Most of the homework question link still applies, re learners, and all of the rest of the comments, including, if you're not doing homework, still follow a published academic textbook, dozens are online free in pdf and/or html. — philipxy, May 24 '20 at 10:34
Hint#2: for relatoional-division problems it is often useful to define the (book) records that you do **not** want to appear in the resultset. Make a bullet list for these conditions, then express them in SQL logic and combine them. Also: `borrowed by all study groups of a single Student,` is ambiguous. (and Venn diagrams don't work here, at least not this way) — wildplasser, May 24 '20 at 10:44

score 1 · Answer 1 · answered May 24 '20 at 10:27

Here is one approach using aggregation:

select b.id, b.title
from books b
inner join borrowed bd on bd.book_id = b.id
inner join studyGroup sg on sg.id = bd.study_group_id
where sg.student_id = 1
group by b.id, b.title
having count(*) = (select count(*) from studyGroup sg1 where sg1.student_id = 1)

score 0 · Accepted Answer · answered May 24 '20 at 10:29

0

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

answered May 24 '20 at 10:29

CarlosSR

1,145
1
10
22

@semiphys This doesn't return the rows described in the question. PS The answers at the link in the question are poor. PS There is a help link in the top banner. – philipxy May 24 '20 at 11:57
1

I downvoted ihis asnwer (I seldom do so) It does not answer the question. (It might anwser a different question, but that is not *the rule of this site* ) – wildplasser May 24 '20 at 12:00
@philipxy I think you should probably check that... it works perfectly. – semiphys May 24 '20 at 12:01
1

@semiphys It might do what you think the specification you gave says. (I can see a certain way a person might misinterpret the specification that would have this query as a solution, but it involves an unreasonably sloppy interpretation of it, or if the query returns what the specifier had in mind, unreasonably sloppy writing of the specification.) PS Too bad there's no [mre]. PS When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. – philipxy May 24 '20 at 12:19

SELECT requiring multiple joins with one to many tables in PostgreSQL

2 Answers2