How to join more than two levels of join in oracle

Question

I have below tables

1.student(student_id,student_name)

+------------+--------------+-----------+
| student_id | Student_name |   class   |
+------------+--------------+-----------+
|          1 | A            | 5TH GRADE |
|          2 | B            | 5TH GRADE |
|          3 | C            | 5TH GRADE |
+------------+--------------+-----------+

2.student_subject_link(student_id,subject_id)

+------------+------------+
| student_id | subject_id |
+------------+------------+
|          1 |        400 |
|          2 |        401 |
|          1 |        401 |
|          2 |        403 |
|          3 |        400 |
|          3 |        401 |
+------------+------------+

3.subject(subject_id,subject_name),

+------------+-----------------+
| subject_id |  subject_name   |
+------------+-----------------+
|        400 | MATHS           |
|        401 | PHYSICS         |
|        402 | CHEMISTRY       |
|        403 | COMPUTERSCIENCE |
|        404 | ENGLISH         |
+------------+-----------------+

4.student_file(student_id,file_id,file_name,file_uploaded_by,file_updated_by)

+------------+---------+-----------+-----------------+-----------------+
| STUDENT_ID | FILE_ID | FILE_NAME | FILE_CREATED_BY | FILE_UPDATED_BY |
+------------+---------+-----------+-----------------+-----------------+
|          1 |     500 | FILEA.TXT | STAFF1          | STAFF2          |
|          2 |     501 | FILEB.TXT | STAFF2          | STAFF2          |
|          3 |     502 | FILEC.TXT | STAFF3          | STAFF2          |
+------------+---------+-----------+-----------------+-----------------+

5.staff(staff_id,staff_name)student_file and staff table are linked by staff_id.file_uploaded_by ,file_created_by has staff_id.

+----------+------------+
| STAFF_ID | STAFF_NAME |
+----------+------------+
| STAFF1   | XX         |
| STAFF2   | YY         |
| STAFF3   | ZZ         |
+----------+------------+

I need to get the below output. Is it possible to achieve this in a single query or do i have to use multiple queries (one for getting subject id and other for file details. I am using this in a spring web application. Can you please let me know the best way to achieve this.

I want to join all those tables to take student_name,List of Subjects he enrolled(comma separated field),file_name,File created by(Staff name from staff table),File Updated by(Staff name from staff table)

+--------------+-------------------------+-----------+-----------------+-----------------+
| STUDENT_NAME |         SUBJECT         | FILE_NAME | FILE_CREATED_BY | FILE_UPDATED_BY |
+--------------+-------------------------+-----------+-----------------+-----------------+
|            1 | MATHS,PHYSICS           | FILEA.TXT | XX              | YY              |
|            2 | PHYSICS,COMPUTERSCIENCE | FILEB.TXT | YY              | YY              |
|            3 | MATHS,PHYSICS           | FILEC.TXT | ZZ              | YY              |
+--------------+-------------------------+-----------+-----------------+-----------------+

Well, what have you tried? Looks like homework, and that is OK, but you need to show some effort. — OldProgrammer, Dec 03 '17 at 16:00
possible duplicate: https://stackoverflow.com/questions/14995000/sql-inner-join-more-than-two-tables — Gholamali Irani, Dec 03 '17 at 16:02

Kaushik Nayak · Accepted Answer · 2017-12-04T04:26:19.917

Try this.

WITH sub AS
  (SELECT student_id,
          LISTAGG(subject_name, ',') WITHIN
   GROUP (
          ORDER BY subject_id) SUBJECT
   FROM
     (SELECT DISTINCT sbl.student_id,
                      sb.subject_id,
                      subject_name
      FROM student_subject_link sbl
      JOIN subject sb ON sb.subject_id = sbl.subject_id )
   GROUP BY student_id )
SELECT stu.student_name,
       MAX(sub.subject) SUBJECT,
       MAX(sf.file_name) FILE_NAME,
       MAX(
             (SELECT stf.staff_name
              FROM staff stf
              WHERE stf.STAFF_ID = sf.FILE_CREATED_BY )) FILE_CREATED_BY,
       MAX(
             (SELECT stf.staff_name
              FROM staff stf
              WHERE stf.STAFF_ID = sf.FILE_UPDATED_BY )) FILE_UPDATED_BY
FROM student stu
JOIN sub ON sub.student_id = stu.student_id
JOIN student_file sf ON sf.student_id = stu.student_id
GROUP BY stu.student_name;

EDIT:

Explanation ( Requested by OP) : student_subject_link and student are related by subject_id. But since the relation is many to many, The operation of concatenation of enrolled subjects is taken in a separate with clause and not included in the joins involving main tables to avoid confusion. LISTAGG function concatenates elements within a group.Distinct is used to avoid duplicates formed because of join. A with clause helps us to construct an inline view which can be reused as if it was a table.

In the select clause , since we need to group by student_name, MAX of all other values are taken which are required for the group by to work. A correlated sub query in select is used to fetch the corresponding staff_name since it cannot be inner joined because of one to many relation.

How to join more than two levels of join in oracle

1 Answers1