Create list of column from one table where only ID's from other table have only a 1 value and not 0 from another table

Question

I have a bunch of different tables I'm using to get info on the the products with labels that only have "use" 0 from another label and filtering on the group_id:

CREATE TABLE Product (id, color) AS
  SELECT 234322, 'green' FROM DUAL UNION ALL
  SELECT 636634, 'blue'  FROM DUAL;

Product_label

product_id	label_id
234322	special
636634	nosale
234322	regular
636634	special
232897	nosale
232897	regular

Labels

id	label_name	use	region_id
345	special	1	567
325	nosale	0	567
690	regular	0	891

Region

id	group_id
567	55
478	55
785	55

Product_region

product_id	region_id
234322	567
636634	567
234322	785
636634	478

The result I'm trying to get is to only get the product_id with the listed names of the label, but for use = 0 for a specific group_id:

product_id	label_id	use	group_id
232897	nosale, regular	0	55

How do I get the right results without showing the product_id's that have a label with use '1' and with '0'? What I've come up with so far gives me the listed label names but how to filter only product_id's without any label with use '1'?

LISTAGG(DISTINCT C.LABEL_NAME, ',') WITHIN GROUP (ORDER BY B.PRODUCT_ID)
OVER (PARTITION BY B.PRODUCT_ID) AS NAMES,
LISTAGG(DISTINCT C.USE, ',') WITHIN GROUP (ORDER BY B.PRODUCT_ID)
OVER (PARTITION BY B.PRODUCT_ID) AS SELECTED

FROM PRODUCT A

LEFT JOIN PRODUCT_LABEL B
ON A.ID = B.PRODUCT_ID

LEFT JOIN LABELS C
ON B.LABEL_ID = C.ID

LEFT OUTER JOIN REGION D
ON C.REGION_ID = D.ID

WHERE D.GROUP_ID = '55';```

Answer 1

Its not clear why you are using analytic functions. You appear to want to aggregate and can use brackets to specify the precedence of the joins and add a filter for use = 0 in the join condition so that the LEFT OUTER JOIN stays an OUTER JOIN (currently, your WHERE D.GROUP_ID = '55' filter changes all the LEFT joins to INNER joins as it is impossible for that filter to be true during an OUTER join):

SELECT p.id AS product_id,
       LISTAGG(l.label_name, ',') WITHIN GROUP (ORDER BY l.label_name)
         AS label_names,
       MAX(l.use) AS use,
       MAX(r.group_id) AS group_id
FROM   product p
       LEFT OUTER JOIN (
         product_label pl
         INNER JOIN labels l
         ON pl.label_id = l.label_name
         INNER JOIN region r
         ON l.region_id = r.id
       )
       ON     p.id  = pl.product_id
          AND l.use = 0
          AND r.group_id = 55
GROUP BY
       p.id

Note: this is untested because your sample data is not in an easily usable format. If you provide the CREATE TABLE and INSERT statements in the question (rather than having the data in tables) then it can be tested.