Sql Server : How to get all children, grandchildren & so on for any Parent

Say i have Merchant Table which has self reference key to itself as ParentFK. So a Merchant can have parent Merchant & Child Merchants.

So now the question is :

How to retrive all children & its all sub level children of any merchant .

I have accomplished using recursive CTE.


———————————————————————————

 
–PRINT ‘Create usp_GetAllChildsForMerchant’
IF EXISTS (SELECT * FROM sysobjects WHERE name = ‘usp_GetAllChildsForMerchant’)  
BEGIN   
–PRINT FUNCTION usp_GetAllChildsForMerchant already exists. So, dropping it’   
DROP  FUNCTION usp_GetAllChildsForMerchant  
END  
GO
CREATE FUNCTION usp_GetAllChildsForMerchant(@mgrID int)
RETURNS TABLE AS RETURN
(
———————-Recursive CTE
WITH MerchantChildsCTE (mKey, mChild)
AS (
select pkey as mKey,parentFK as mParent from Merchant where parentFK = @mgrID
UNION ALL select m.PKey as mKey,m.ParentFk as mParent from Merchant m inner join MerchantChildsCTE c on m.ParentFK=c.mKey
)
select @mgrID as mKey UNION SELECT mKey FROM MerchantChildsCTE
————————————-
)
GO

————————————————————————–


Final Retrieval Query : select mkey from usp_GetAllChildsForMerchant(2)

Advertisements