I want to make one-to-many join from many-to-many. Two tables samples:

PRODUCTION:

ORDERS:

If I write a sample query like

SELECT ORDERS.ORDER, ORDER.AMOUNT_TO_PRODUCE, SUM(PRODUCTION.PRODUCED_AMOUNT), ORDERS.PRODUCT
FROM ORDERS JOIN PRODUCTION ON ORDERS.PRODUCT_ID = PRODUCTION.PRODUCT_ID
GROUP BY ORDERS.ORDER, ORDER.AMOUNT_TO_PRODUCE, ORDERS.PRODUCT

I get

But I want

CodePudding user response：

It looks like you want to find the total amount produced and join it to the first row in the orders table per order and product id. If that's the case, you should:

1. sum the production rows before joining them
2. identify the first row in the orders table per order and product id, which you can do by using the row_number() analytic function
3. join both together, which will involve an outer join of the summed production rows to the orders table

You can do this like so:

WITH production AS (SELECT 'Order1' order_num, 1000 produced_amount, 'ProductID1' product_id FROM dual UNION ALL
                    SELECT 'Order1' order_num, 1000 produced_amount, 'ProductID1' product_id FROM dual UNION ALL
                    SELECT 'Order1' order_num, 5000 produced_amount, 'ProductID2' product_id FROM dual),
         orders AS (SELECT 'Order1' order_num, 600 amount_to_produce, 'Product1 - note1' product, 'ProductID1' product_id FROM dual UNION ALL
                    SELECT 'Order1' order_num, 600 amount_to_produce, 'Product1 - note2' product, 'ProductID1' product_id FROM dual UNION ALL
                    SELECT 'Order1' order_num, 600 amount_to_produce, 'Product1 - note3' product, 'ProductID1' product_id FROM dual UNION ALL
                    SELECT 'Order1' order_num, 5000 amount_to_produce, 'Product2' product, 'ProductID2' product_id FROM dual),
    -- end of mimicking your tables; main query below
    prdctn_amts AS (SELECT order_num,
                           SUM(produced_amount) total_produced_amount,
                           product_id
                    FROM   production
                    GROUP BY order_num,
                             product_id),
     order_dets AS (SELECT order_num,
                           amount_to_produce,
                           product,
                           product_id,
                           row_number () OVER (PARTITION BY order_num, product_id ORDER BY product) rn
                    FROM   orders)
SELECT o.order_num,
       o.amount_to_produce,
       NVL(p.total_produced_amount, 0)
       total_produced_amount,
       o.product
FROM   order_dets o
       LEFT OUTER JOIN prdctn_amts p ON o.order_num = p.order_num
                                        AND o.product_id = p.product_id
                                        AND o.rn = 1
ORDER BY o.order_num,
         o.product_id,
         o.rn;

which produces the following output:

ORDER_NUM AMOUNT_TO_PRODUCE TOTAL_PRODUCED_AMOUNT PRODUCT
--------- ----------------- --------------------- ----------------
Order1                  600                  2000 Product1 - note1
Order1                  600                     0 Product1 - note2
Order1                  600                     0 Product1 - note3
Order1                 5000                  5000 Product2

N.B. You wouldn't need the first two subqueries in the with clause; I've added them so I could mimic your two tables, whereas you would just reference the two tables directly. Also, as ORDER is a reserved word in Oracle, I avoided having to use " to encapsulate the column name every time I needed to reference it by changing the column name to ORDER_NUM.