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