-1

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.

jasmeet
  • 37
  • 4
  • Does this answer your question? [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result) – philipxy Jun 03 '22 at 21:56
  • Please in code questions give a [mre]. Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy Jun 03 '22 at 21:58

1 Answers1

0

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;
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40