Query Join help

Transact-SQL
1

Hey Guys,

I have the below Query 1 that i want to join to Query 2, but i am struggling to get it to work. I am new to SQL just feeling my way through as I do a course! Any and all help appreciated. If I am going about it wrong please let me know

Would be looking to add query 2 onto the end of query 1 table when 'peg.demand_order_number' matches 'h.order_number'

Query 1

SELECT *
FROM   (SELECT peg.plant_sys,
               peg.so_tran_order_number,
               peg.so_tran_line_number,
               peg.pegging_level_no,
               peg.demand_order_number,
               peg.supply_order,
               peg.supply_material,
               peg.allocated_qty,
               peg.ext_cost,
               pegging_seq_2
                           
        FROM   og_surf_snop_dm.sop_mps_pegging_data_mv peg
        WHERE  peg.plant_sys = '0166'
               AND peg.so_tran_order_number = '4511692284'
               AND peg.so_tran_line_number = '11'
        UNION ALL
        
        SELECT peg1.plant_sys,
               peg1.so_tran_order_number,
               peg1.so_tran_line_number,
               peg1.pegging_level_no,
               peg1.demand_order_number,
               'issued'                              supply_order,
               mt.material                           SUPPLY_MATERIAL,
               -Sum(trans_qty)                       allocated_qty,
               To_char(-Sum(trans_cost), '999999.99')ext_cost,
               pegging_seq_2
        FROM   og_surf_snop_dm.inv_mtl_trans_12r_t MT
              
               INNER JOIN (SELECT peg.plant_sys,
                                  peg.so_tran_order_number,
                                  peg.so_tran_line_number,
                                  peg.pegging_level_no,
                                  peg.demand_order_number,
                                  peg.src_nam_sys,
                                  Max(pegging_seq_2)PEGGING_SEQ_2
                           FROM   og_surf_snop_dm.sop_mps_pegging_data_mv peg
                           WHERE  demand_type_desc = 'Dependent reservation'
                           GROUP  BY peg.plant_sys,
                                     peg.so_tran_order_number,
                                     peg.so_tran_line_number,
                                     peg.pegging_level_no,
                                     peg.demand_order_number,
                                     peg.src_nam_sys)Peg1
                       ON peg1.plant_sys = mt.plant_sys
                          AND peg1.src_nam_sys = mt.src_nam_sys
                          AND peg1.demand_order_number = mt.sap_wo_nbr
                                                
                                                     
        WHERE  mt.transaction_type_id IN ( '261', '262' )
        GROUP  BY peg1.plant_sys,
                  peg1.so_tran_order_number,
                  peg1.so_tran_line_number,
                  peg1.pegging_level_no,
                  peg1.demand_order_number,
                  mt.material,
                  pegging_seq_2);

Query 2

SELECT h.order_number,
       i.supply_material,
       Sum(CASE
             WHEN h.operation_qty = h.confirmed_qty THEN
             h.confirmed_1 + h.confirmed_2 + h.confirmed_3
             ELSE 0
           END) AS "Booked Hours",
       Sum(CASE
             WHEN h.operation_qty = h.confirmed_qty THEN 0
             ELSE h.standard_1 + h.standard_2 + h.standard_3 + h.standard_4
           END) AS "Remaining Hours",
       Sum(CASE
             WHEN h.operation_qty = h.confirmed_qty THEN
             h.confirmed_1 + h.confirmed_2 + h.confirmed_3
             ELSE h.standard_1 + h.standard_2 + h.standard_3 + h.standard_4
           END) AS "Predicted Hours"
FROM   og_surf_snop_dm.prod_order_operation_t h
       INNER JOIN og_surf_snop_dm.sop_mps_pegging_data_mv i
               ON i.demand_order = h.order_number
GROUP  BY h.order_number,
          i.supply_material;
2

In general, you can do as shown below. The main thing you have to do is to identify the matching columns in the two queries. Once you get that working, you can look for efficiencies - you may not need to do the two cte's, instead might be able to write just one query.

;WITH cte1 AS
(
    -- your first query here
),
cte2 AS
(
    -- your second query here
)
SELECT
    *
FROM
    cte1 AS c1
    INNER JOIN cte2 AS c2 ON -- you may need full join, or left join etc.
        c1.SomeColumn = c2.MatchingColumn
        AND c1.AnotherColumn = c2.MatchingOtherColumn -- etc.