Combine an SQL Query with JOINS and a SQL Query with a LISTAGG?

Question

I have two separate Oracle SQL queries that will work independently, but I cannot figure out how to combine them in one.

Query one: a JOIN of fields from 4 tables.

SELECT 
       table01.guid,
       table02.kbcid, 
       table01.name,
       table01.filename, 
       table01.filesize,
       table01.essencegroup,
       table01.checksum,
       table01.type, 
       table02.kbcid, 
       table03.kbcnm,
       table03.parentkbcid, 
       table03.kbcpath,
       table04.metaid,
       table04.metavalue,
FROM table02
INNER JOIN table01 ON table02.guid=table01.guid
INNER JOIN table03 ON table02.kbcid=table03.kbcid
INNER JOIN table04 ON table01.guid=table04.guid;

Query two: a table with a one to many relationship, using LISTAGG to concat rows.

SELECT 
       table5.guid,
LISTAGG(TO_CHAR(table5.term), ',') WITHIN GROUP (ORDER BY table5.guid) AS 
        keywords
FROM table5
Group BY table5.guid;

Ideally, I would like to combine these two queries to return one set of results. But I keep getting various errors.

Can someone suggest the right way to format this query?

Answer 1

I don't know relation between tables but I think you can use the WITH clause! Here an example, fix it with tables relationship:

WITH tab5 AS (
SELECT 
   table5.guid,
   LISTAGG(TO_CHAR(table5.term), ',') WITHIN GROUP (ORDER BY table5.guid) AS 
    keywords
FROM table5
GROUP BY table5.guid
)
SELECT 
   table01.guid,
   table02.kbcid, 
   table01.name,
   table01.filename, 
   table01.filesize,
   table01.essencegroup,
   table01.checksum,
   table01.type, 
   table02.kbcid, 
   table03.kbcnm,
   table03.parentkbcid, 
   table03.kbcpath,
   table04.metaid,
   table04.metavalue,
 FROM table02
 INNER JOIN table01 ON table02.guid=table01.guid
 INNER JOIN table03 ON table02.kbcid=table03.kbcid
 INNER JOIN table04 ON table01.guid=table04.guid
 --Put relation to other tables
 INNER JOIN tab5 ON tabl01.guid = tab5.guid;