select & Join tables and combine partially duplicated results-CodePudding

I have 5 tables.

I am trying to select and join so that I have one row for each customer returned. Here's what I have so far:

SELECT CUSTOMER.CUSTFNAME || ' ' || CUSTOMER.CUSTLNAME AS "CUSTOMER",
ORDERITEM.ITEMDESC AS "ITEMS PURCHASED", 
RETURNITEM.ITEMDESCS "RETURNS",
STATES.STATENAME
FROM CUSTOMER 
INNER JOIN ORDER ON CUSTOMER.CUSTID = ORDER.CUSTID
INNER JOIN ORDERITEM ON ORDER.OITEMID = ORDERITEM.OITEMID
INNER JOIN RETURN ON CUSTOMER.CUSTID = RETURN.CUSTID
INNER JOIN RETURNITEM ON RETURN.RITEMID = RETURNITEM.RITEMID
INNER JOIN STATES ON CUSTOMER.STATEID = STATES.STATEID;

Which works, except...

I have a single customer who has two orders (two items) and two returns (two return items). Rather than seeing:

---Customer------ITEMS PURCHASED------------RETURNS

Joe Snuffy       widget 1                   widget 1
joe snuffy       widget 2                   widget 1
joe snuffy       widget 1                   widget 2
joe snuffy       widget 2                   widget 2

My end goal is to see:

---Customer------ITEMS PURCHASED------------RETURNS

Joe Snuffy       widget 1, widget 2         widget 1, widget 2

Is there a way to do that? Preferably one that isn't a super complicated function or something haha...

TIA!

CodePudding user response：

It's too bad your are on 18c instead of 19c, you could use the new DISTINCT flag on LISTAGG. As it is, you just need to split it up a bit:

SELECT CUSTOMER.CUSTFNAME || ' ' || CUSTOMER.CUSTLNAME AS "CUSTOMER",
o.purchased AS "ITEMS PURCHASED", 
r.returned "RETURNS",
STATES.STATENAME
FROM CUSTOMER c
INNER JOIN (SELECT o.custid, 
                   LISTAGG(oi.itemdesc, ', ') WITHIN GROUP (ORDER BY oi.itemdesc) AS PURCHASED
            FROM ORDER o
            INNER JOIN ORDERITEM oi ON o.OITEMID = oi.OITEMID
            GROUP BY o.custid) o ON c.custid = o.custid
INNER JOIN (SELECT r.custid,
                   LISTAGG(ri.itemdesc, ', ') WITHIN GROUP (ORDER BY ri.itemdesc) AS RETURNED
            FROM RETURN r
            INNER JOIN RETURNITEM ri ON r.RITEMID = ri.RITEMID
            GROUP BY r.custid) r ON c.custid = r.custid
INNER JOIN STATES ON CUSTOMER.STATEID = STATES.STATEID;

In this code, I just LISTAGG the individual lists and then join it all together.