I would like an SQL query that returns 'Orders' based on the associated 'Product', the 'Product.DepositId' has to equal an exact integer value (for example Product.DepositId = 1).

If the associated 'Product.DepositId' is null, then the query needs to go up the Product ladder get the parent 'Product' using 'Product.ParentId' and so on.

• A 'Product' parent hierarchy can go 'N' tiers/layers. (for example Child -> ChildParent -> ChildParent -> Final Parent)
• Multiple 'Product' children can be associated to the same Parent
• Only the top most parent 'Product' will have a DepositId. So if the 'Product.ParentId' is null then the 'Product.DepositId' will not be null
• An 'Order' can be associated with a child 'Product' or with a parent 'Product'. (Parents can have orders as well.)

For example (to make the example simple I used integer ids instead of uniqueidentifier)

Products

Id  ParentId  DepositId
1   NULL      10
2   NULL      20
3   1         NULL
4   2         NULL
5   1         NULL
6   3         NULL

Orders

Id      ProductId
1001    1
1002    2
1003    3
1004    4
1005    5
1006    6

Expected Result Orders with DepositId = 10

OrderId  ProductId
1001     1           --Because Product 1 DepositId = 10
1003     3           --Because Product 3 is child of Product 1
1005     5           --Because Product 5 is child of Product 1
1006     6           --Because Product 6 is child of Product 3 which in 
                       turn is a child of Product 1

CodePudding user response：

This calls for a recursive CTE.

;with rcte_products as (
  select Id as ProductId, ParentId
  , DepositId
  , 0 as Lvl
  , Id as BaseId
  from Products
  where DepositId = 10
  
  union all
  
  select t.Id, t.ParentId
  , c.DepositId
  , c.Lvl 1
  , c.BaseId
  from rcte_products c
  join Products t on t.ParentId = c.ProductId
)
select 
  o.Id as OrderId
, o.ProductId
from rcte_products c
join Orders o 
  on o.ProductId = c.ProductId
order by o.Id;

Demo on db<>fiddle here