How create two supersets from different tables

Question

these are my tables.

Main
------
mainid(PK)     
1           
2                    
3           


Emp
-----
empid(PK)    Name      
1            Dave            
2            Dan            
3            Mark            
4            Steve     
5            Elvis
6            Jacob

Tools
-----
toolid(PK)         
1                       
2                        
3                        
4                 
5            
6            


MainEmp
-----
mainid(FK)   empid(FK)      
1            1            
1            2            
2            3            
2            4            
3            5
3            6

MainTools
-----
mainid(FK)   tools(FK)      
1            1            
1            2            
1            3            
2            4            
3            5
3            6

I want to achieve this query result

mainid       emp            tools     
1            Dave, Dan       1,2,3     
2            Mark, Steve     4            
3            Elvis,Jacob     5,6

I'm using following sql

 select m.mainid,
 listagg(emp.name, ',') within group (order by emp.empid)
 from main m join
 mainemp me
 on m.mainid = me.mainid join
 emp e
 on e.empid = me.empid
 group by m.mainid

It does work if I try to display main with emp or main with tools. However I can't figure it out how to connect it together. Please help

Answer 1

You need to do the aggregations in subqueries, and then join them together:

select m.*, n.names, mt.tools
from main m left outer join
     (select me.mainid, listagg(e.name, ',') within group (order by e.name) as names
      from MainEmp me join
           Employees e
           on e.empid = me.empid
      group by me.mainid
     ) n
     on m.mainid = n.mainid
     (select mt.mainid, listagg(mt.tools, ',') within group (order by mt.tools) as tools
      from MainTools mt
      group by mt.mainid
     ) mt
     on m.mainid = mt.mainid

Answer 2

If is oracle 11g,you can also do like this:

SELECT DISTINCT A.MAINID,
WM_CONCAT(DISTINCT C.NAME) OVER(PARTITION BY A.MAINID) AS NAMES,
WM_CONCAT(DISTINCT D.TOOLS) OVER(PARTITION BY A.MAINID ) AS TOOLS
FROM 
MAIN A JOIN MAINEMP B
ON A.MAINID=B.MAINID
JOIN EMP C
ON B.EMPID=C.EMPID
JOIN MAINTOOLS D
ON A.MAINID=D.MAINID