Join 3 or more tables with Comma separated values in Oracle-CodePudding

I've 3 tables (One parent and 2 childs) as below

Student(Parent)

StudentID | Name |  Age
    1     |  AA  |  23
    2     |  BB  |  25
    3     |  CC  |  27

Book(child 1)

BookID    | SID | BookName |  BookPrice
    1     |  1  |  ABC     |    20
    2     |  1  |  XYZ     |    15
    3     |  3  |  LMN     |    34
    4     |  3  |  DDD     |    90

Pen(child 2)

 PenID    | SID | PenBrandName |  PenPrice
    1     |  2  |      LML     |    20
    2     |  1  |      PARKER  |    15
    3     |  2  |      CELLO   |    34
    4     |  3  |      LML     |    90

I need to join the tables and get an output as Below

StudentID | Name |  Age | BookNames  | TotalBookPrice  | PenBrands  | TotalPenPrice
    1     |  AA  |  23  |  ABC, XYZ  |       35        |   PARKER   |       15 
    2     |  BB  |  25  |    null    |       00        | LML, CELLO |       54
    3     |  CC  |  27  |  LMN, DDD  |       124       |   LML      |       90

This is the code i tried :

Select s.studentID as "StudentID", s.name as "Name", s.age as "AGE", 
LISTAGG(b.bookName, ',') within group (order by b.bookID) as "BookNames",
SUM(b.bookPrice) as "TotalBookPrice",
LISTAGG(p.penBrandName, ',') within group (order by p.penID) as "PenBrands",
SUM(p.penPrice) as "TotalPenPrice"
FROM Student s
LEFT JOIN BOOK b ON b.SID = s.StudentID
LEFT JOIN PEN p ON p.SID = s.StudentID
GROUP BY s.studentID, s.name, s.age

The result i get has multiple values of Book and Pen (cross product result in multiple values)

StudentID | Name |  Age |      BookNames    | TotalBookPrice  |     PenBrands     | TotalPenPrice
    1     |  AA  |  23  |  ABC,ABC,XYZ,XYZ  |       35        |   PARKER,PARKER   |       15

Please let me know how to fix this.

CodePudding user response：

Instead of Joining the tables and doing aggregation, You have to aggregate first and then join your tables -

Select s.studentID as "StudentID", s.name as "Name", s.age as "AGE", 
"BookNames",
"TotalBookPrice",
"PenBrands",
"TotalPenPrice"
FROM Student s
LEFT JOIN (SELECT SID, LISTAGG(b.bookName, ',') within group (order by b.bookID) as "BookNames", 
                  SUM(b.bookPrice) as "TotalBookPrice"
             FROM BOOK
            GROUP BY SID) b ON b.SID = s.StudentID
LEFT JOIN (SELECT SID, LISTAGG(p.penBrandName, ',') within group (order by p.penID) as "PenBrands", 
                  SUM(p.penPrice) as "TotalPenPrice"
             FROM PEN
            GROUP BY SID) p ON p.SID = s.StudentID;