Oracle | Retrieval of records from tables having One to many relationship

Question

I have two tables which share one-to-many relationship. MY_FACT is the parent table whereas MY_RMDETAILS is the child table having multiple records for a single parent record.

Table MY_FACT:

FACT_ID	FACT_DATE	TOTAL_DEMAND
1000	21/04/2022	500
2000	21/04/2022	500

Table MY_RMDETAILS:

RM_ID	FACT_ID	PROMISE_QTY	REQUEST_QTY	RM_ITEM_NAME
200	1000	500	500	RM1
201	1000	400	500	RM2
202	1000	500	500	RM3
203	1000	400	500	RM4
300	2000	500	500	RM1
301	2000	500	500	RM2
302	2000	500	500	RM3
303	2000	500	500	RM4

I need to write a query to have below output.

Logic:

If MY_RMDETAILS.PROMISE_QTY is less than MY_RMDETAILS.REQUEST_QTY, the supply is insufficient.

So for any given MY_FACT record, if any one of its children records from MY_RMDETAILS has PROMISE_QTY less than REQUEST_QTY, the flag SUPPLY_SUFFICIENT in output should be N else it should be Y.

And INSUFFICIENT_RMs column in output should show the MY_RMDETAILS.RM_ITEM_NAME of "insufficient" records as comma separated format.

EXPECTED OUTPUT:

FACT_ID	FACT_DATE	TOTAL_DEMAND	SUPPLY_SUFFICIENT?	INSUFFICIENT_RMs
1000	21/04/2022	500	N	RM2,RM4
2000	21/04/2022	500	Y

Please help. Thanks in advance.

Answer 1

You can try to use subquery with condition aggregate function.

SELECT t2.*,
       CASE WHEN t1.INSUFFICIENT_cnt > 0 THEN 'N' ELSE 'Y' END,
       t1.INSUFFICIENT_RMs
FROM (
    SELECT FACT_ID,
        LISTAGG(CASE WHEN PROMISE_QTY < REQUEST_QTY THEN RM_ITEM_NAME END, ', ')  WITHIN GROUP (ORDER BY RM_ID)  INSUFFICIENT_RMs,
        COUNT(CASE WHEN PROMISE_QTY < REQUEST_QTY   THEN RM_ITEM_NAME END) INSUFFICIENT_cnt
    FROM MY_RMDETAILS
    GROUP BY FACT_ID
) t1 INNER JOIN MY_FACT t2
ON t1.FACT_ID = t2.FACT_ID