Interesting Sql Server Question : Parent Child Hierarchy

I explain a bit on contextual Domain here :
Say i have Orders sold to customers by Merchants.
So Financial related infomation of Merchants are saved in MerchantFinancialSettings. Here a merchant can have more than one financial info(different bank accounts), so more than one [MerchantFinancialSettings].
Normal Requirement :
Coming to requirement, u have to define a search functionality based on few parameters of
– Orders say BookingDate etc
– MerchantFinancialSettings say AccountNr
So the query looks like pretty simple
——————–
Select OrderNo as this_.OrderNo ,mrch.Name, COALESCE(PaymentReference,Reference,ContractNr,CustomerAccountNr) as ReferenceNo
FROM [Order] this_
inner join [Merchant] mrch on this_.MerchantFK = mrch.PKey
inner join [MerchantFinancialSettings] settings_ on settings_.MerchantFK = mrch.PKey
Where this_.BookingDate = ’05-30-2013′ and settings_.AccountNr=’1234345435345′
———————————
Coming back to new requirement,
While searching for a Order with search criteria of Merchant – Account Nr, if it doesnt match, the query should perform on parents of its merchant. ok bit more context on Merchants here – Merchant table has a self refernce Key ParentFK which means, a Merchant may have n number of childs & m number of children too.
Say Order is booked to merchant – McDonald’s Italy. Where as Mc.Donald’s can be a parent to McDonald’s Italy. So Sometimes, account details can be stored for Mc.Donald’s instead of McDonald’s Italy.
In Simple, problem here :
while we search for each record of
Order with order criteria matched &
Linked Merchant matched with MerchantFinancialSettings criteria
Also Match should perform on parents of Linked Merchant – MerchantFinancialSettings criteria
Idea of solution :
Instead of joining Order to linked Merchant(Order.MerchantFK), we should join to – MerchantFk + Its parents

I Tried with a solution suing CTE, which failed eventually because of some limisations set by SQl Server
———————————————————————————
So my second approach which got failed was..
Declare @mkey int
Declare @OrderNo int
Declare @MerchantName varchar(128)
Declare @ReferenceNo varchar(64)

WITH CTE(MerchantFK) AS
(
SELECT ParentFK from Merchant where PKey=@child
UNION ALL
SELECT m.PKey
from Merchant m
INNER JOIN CTE c ON m.Pkey= c.MerchantFK
)

Select @mKey=this_.MerchantFK, @OrderNo=OrderNo ,@MerchantName = mrch.Name, @ReferenceNo = COALESCE(PaymentReference,Reference,ContractNr,CustomerAccountNr)
FROM [Order] this_
inner join [Merchant] mrch on mrch.PKey in (SELECT MerchantFK FROM CTE )
inner join [MerchantFinancialSettings] settings_ on settings_.MerchantFK = mrch.PKey

select @OrderNo as OrderNo, @MerchantName as MerchantName,@ReferenceNo as ReferenceNo
————————————————

Challenges Faced :
1) I used Common Table Expression(CTE) to get the Parents of the merchant, but since it needs parameterised, i used a local varaible
3) This local variable has to be set for every main record execution. So we cannot include local variable assignment along with normal column retrieval, so declared few more parameters to carry the output
Why It failed :
Donot find the mechanism, where i can retrive mulitple records along with scalor variable setting.

Ultimate Solution that worked out:1) Declared a Sql function which takes merhcant Key & gives me all the Parents
2) Then used it in the same query
Sql Function :
—————–
IF EXISTS (SELECT * FROM sysobjects WHERE name = ‘usp_GetAllParentsForMerchant’)
BEGIN
DROP FUNCTION usp_GetAllParentsForMerchant
END
GO

CREATE FUNCTION usp_GetAllParentsForMerchant(
@child int
) RETURNS @PARENTS TABLE (
[parent] int
) AS BEGIN
DECLARE @parent int

INSERT @PARENTS SELECT @child
SELECT @parent = ParentFk FROM Merchant WHERE PKey= @child

WHILE @@ROWCOUNT = 1 BEGIN
INSERT @PARENTS SELECT @parent

SELECT @child = @parent
SELECT @parent = ParentFk FROM Merchant WHERE PKey = @child
END

RETURN
END
Go
—————–
Normal Query looks like
Select this_.MerchantFK, OrderNo , mrch.Name, COALESCE(PaymentReference,Reference,ContractNr,CustomerAccountNr)
FROM [Order] this_
inner join [Merchant] mrch on mrch.PKey in (SELECT MerchantFK FROM usp_GetAllParentsForMerchant(this_.MerchantFK) )
inner join [MerchantFinancialSettings] settings_ on settings_.MerchantFK = mrch.PKey

Happy Querying !

Advertisements