Complex join with multiple one to many relationships-CodePudding

I want to pull some orders with their associated items and notes.

Each order can have one or many items and none or many notes.

Here's some example data;

Orders:

OrderNumber
1
2
3

Items:

OrderNumber	Description
1	Laptop
1	TV
2	Projector
3	Laptop
3	Projector

Notes:

OrderNumber	Note
2	Wheelchair access is required
2	Client will be on site 2 hrs before doors open
3	Tea and coffee is to be served at 10am

Here's what I expect the output to look like:

OrderNumber	Item	Note
1	Laptop	NULL
1	TV	NULL
2	Projector	Wheelchair access is required
2	NULL	Client will be on site 2 hrs before doors open
3	Laptop	Tea and coffee is to be served at 10am
3	Projector	NULL

I've tried a bunch of approaches using cross apply, and rank but have been unable to achieve my desired results.

Here's an example of one of the techniques which works but is a mess and will only work for a max of 5 items or notes. I think introducing a CTE might be the answer?

SELECT
    , ER101_ORD_NBR
    , ER101_DESC
    , RANK () OVER (PARTITION BY ER101_ORG_CODE, ER101_EVT_ID, ER101_ORD_NBR ORDER BY ER101_DESC)
        Order_Desc_Rank
INTO
    #order_items
FROM
    ER101_ACCT_ORDER_DTL

SELECT
    , CC025_ORDER
    , CC025_NOTE_TEXT
    , RANK () OVER (ORDER BY CC025_NOTE_TEXT)
        Note_Rank
INTO
    #notes
FROM
    CC025_NOTES_EXT
    
DECLARE @i_items INT, @i_notes INT
SELECT @i_items = MAX(Order_Desc_Rank) FROM #order_items
SELECT @i_notes = MAX(Note_Rank) FROM #notes

SELECT
    ER100_ORD_NBR
    , ER101_DESC
    , CC025_NOTE_TEXT
FROM
    ER100_ACCT_ORDER
CROSS APPLY
(
    SELECT 1 _RANK
    UNION ALL SELECT 2 WHERE @i_items >= 2 OR @i_notes >= 2
    UNION ALL SELECT 3 WHERE @i_items >= 3 OR @i_notes >= 3
    UNION ALL SELECT 4 WHERE @i_items >= 4 OR @i_notes >= 4
    UNION ALL SELECT 5 WHERE @i_items >= 5 OR @i_notes >= 5
) ZZZ
OUTER APPLY
(
    SELECT
        *
    FROM
        #order_items
    WHERE
        ER100_ORD_NBR = ER101_ORD_NBR   
        AND ZZZ._RANK = Order_Desc_Rank
) ER101
OUTER APPLY
(
    SELECT
        *
    FROM
        #notes
    WHERE
        CC025_ORDER = ER100_ORD_NBR
        AND Note_Rank = ZZZ._RANK
) CC025

CodePudding user response：

You don't seem to have a column here you can use for ordering so I just decided to use Description and Note for the relevant tables. I had to leverage ROW_NUMBER to generate a value to use for ordering. You could skip this step if your real data has something you can use. Here is a full working example of how you can use a full outer join to accomplish this. Also notice how I created tables and sample data to work with. This is how you should post your questions in the future as it makes easy for others to help.

declare @Orders table 
(
    OrderNumber int
)
insert @Orders values (1), (2), (3)

declare @Items table 
(
    OrderNumber int
    , Description varchar(20)
)
insert @Items values
(1, 'Laptop')
, (1, 'TV')
, (2, 'Projector')
, (3, 'Laptop')
, (3, 'Projector')


declare @Notes table 
(
    OrderNumber int
    , Note varchar(100)
)
insert @Notes values
(2, 'Wheelchair access is required')
, (2, 'Client will be on site 2 hrs before doors open')
, (3, 'Tea and coffee is to be served at 10am')
;

with cteItems as
(
    select i.OrderNumber
        , i.Description
        , RowNum = ROW_NUMBER() over(partition by o.OrderNumber order by i.Description)
    from @Orders o
    join @Items i on i.OrderNumber = o.OrderNumber
)
, cteNotes as
(
    select n.OrderNumber
        , n.Note
        , RowNum = ROW_NUMBER() over(partition by o.OrderNumber order by n.Note)
    from @Orders o
    join @Notes n on n.OrderNumber = o.OrderNumber
)
select OrderNumber = coalesce(i.OrderNumber, n.OrderNumber)
    , i.Description
    , n.Note
from cteItems i
full outer join cteNotes n on i.OrderNumber = n.OrderNumber and i.RowNum = n.RowNum
 order by coalesce(i.OrderNumber, n.OrderNumber)

CodePudding user response：

To prevent the cartesian result in your output due to the possibility of multiple notes, you would need to pre-aggregate them into a single row per order. If all you are looking for of the items is a comma list, then same applies. Then, the rest should be a simple join.

select
      o.orderNumber,
      i.NumberItems,
      i.AllItems, 
      coalesce( n.NumberNotes, 0 ) NumberNotes,
      coalesce( n.AllNotes, '' ) AllNotes
   from
      orders o

         JOIN
         ( select
                 orderNumber,
                 count(*) NumberItems,
                 string_agg( description, ', ') AllItems
              from
                 items
              group by 
                 orderNumber ) i
           on o.orderNumber = i.orderNumber

         LEFT JOIN
         ( select
                 orderNumber,
                 count(*) NumberNotes,
                 string_agg( note, ', ') AllNotes
              from
                 notes
              group by 
                 orderNumber ) n
            on o.orderNumber = n.orderNumber

I did a LEFT-JOIN to the pre-aggregate of notes since they are not required, but normal join for items since that is the basis of any actual order.

CodePudding user response：

Have you tried this?

SELECT o.OrderNumber
, i.Description AS Item
, n.Note
FROM Orders o 
    INNER JOIN Items i ON 
        i.OrderNumber = o.OrderNumber
    LEFT JOIN Notes n ON 
        n.OrderNumber = o.OrderNumber