CODEWITHSUNDEEP
sqlsql-servert-sql
Ozan Yurtsever
Ozan Yurtsever

Reputation: 1304

Outer apply to distribute rows

I have a table which holds the name of different products such as ;

      ItemName
1   FRESH BREAD
2   FRESH TOMATO
3   FRESH POTATO

And I have two more different tables where is located some other data and I am joining them with this query;

select shipment.RutPlanId
        ,shipment.ShipmentDateTime
        ,shipment.DeliveryLocationName
        ,shipment.Quantity
        from TABLE_SHIPMENT as shipment
        left join TABLE_ITEM as item on item.No_ = shipment.ItemNo
        where shipment.RutPlanId = 2 and item.Description like '%FRESH%'

And query result of the join is;

       RutPlanId    ShipmentDateTime            DeliveryLocationName    ItemName       Quantity
  1        2       2020-03-11 00:00:00.000            ZONGULDAK        FRESH BREAD       110
  2        2       2020-03-11 00:00:00.000             KANYON          FRESH TOMATO      189

I want to combine the first table of product names with the result of above joined tables and get another result like this;

RutPlanId   ShipmentDateTime    DeliveryLocationName    ItemName     Quantity
2             2020-03-11              KANYON           FRESH BREAD      0
2             2020-03-11              KANYON           FRESH POTATO     0
2             2020-03-11              KANYON           FRESH TOMATO    189
2             2020-03-11              ZONGULDAK        FRESH BREAD     110
2             2020-03-11              ZONGULDAK        FRESH POTATO     0
2             2020-03-11              ZONGULDAK        FRESH TOMATO     0

To achive the result at above, I have tried to write a query

select RootInfo.RutPlanId
        ,RootInfo.ShipmentDateTime
        ,RootInfo.DeliveryLocationName 
        ,product.ItemName
        ,ISNULL(shipment.Quantity,0) Quantity
                --select vt.Type  
    from TABLE_PRODUCT_NAMES as product
    left join TABLE_ITEM as item on item.ItemName = product.ItemName
    left join TABLE_SHIPMENT as shipment on shipment.ItemNo = item.No_ and otm.RutPlanId = 2
    outer APPLY(
                select *
                from TABLE_SHIPMENT pl
                where pl.RutPlanId= 2
                )RootInfo
    group by RootInfo.RutPlanId
        ,RootInfo.ShipmentDateTime
        ,RootInfo.DeliveryLocationName
        ,shipment.Quantity
        ,product.ItemName

But the above query gives me a result which is not I wanted. It gives something like this;

RutPlanId   ShipmentDateTime    DeliveryLocationName         ItemName         Quantity
    2             2020-03-11              KANYON           FRESH BREAD          0
    2             2020-03-11              KANYON           FRESH POTATO        110
    2             2020-03-11              KANYON           FRESH TOMATO        189
    2             2020-03-11              ZONGULDAK        FRESH BREAD          0
    2             2020-03-11              ZONGULDAK        FRESH POTATO        110
    2             2020-03-11              ZONGULDAK        FRESH TOMATO        189

How can I achieve my goal and what I should fix at my last query? Thanks in advance.

Upvotes: 1

Views: 33

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81960

How about a CROSS JOIN ?

Example

 ;with cte as (
   -- Your Original Query here
 )
 Select A.[RutPlanId]
       ,A.[ShipmentDateTime]
       ,A.[DeliveryLocationName] 
       ,B.[ItemName]
       ,[Quantity] = case when A.[ItemName]=B.[ItemName] then [Quantity] else 0 end
  From  cte A
  Cross Join Products B

Returns

RutPlanId   ShipmentDateTime        DeliveryLocationName    ItemName      Quantity
2           2020-03-11 00:00:00.000 ZONGULDAK               FRESH BREAD   110
2           2020-03-11 00:00:00.000 KANYON                  FRESH BREAD   0
2           2020-03-11 00:00:00.000 ZONGULDAK               FRESH TOMATO  0
2           2020-03-11 00:00:00.000 KANYON                  FRESH TOMATO  189
2           2020-03-11 00:00:00.000 ZONGULDAK               FRESH POTATO  0
2           2020-03-11 00:00:00.000 KANYON                  FRESH POTATO  0

Upvotes: 1

Related Questions