asked on

Get Parent in hierarchy for Given Child Value.

Hi,
ItemCode ItemName ItemGroup
SFD Softdrink
CCL Coca Cola SFD
FNT Fanta SFD
SPR Sprite SFD
SPZ Sprite Zero SPR
ACL Alcohol
TQL Tequila ACL
VDK Vodka ACL

How to get all the list of the parent of SPZ?

Thank you.

Louis LIETAER

select * from thetablename where Itemcode=(select ItemGroup from thetablename where ItemCode='SPZ')

emi_sastra

ASKER

Hi louislietaer,

How depth it could be using the code?
Just imagine a member get member system.

Thank you.

ASKER CERTIFIED SOLUTION

Louis LIETAER

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sharath S

emi_sastra,
I hope you understand CTE and how to use it for the kind of questions you have. The link provided by louislietaer is good one and contains lot of examples. Have a look.

-- http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/recursivequeriesinsql1999andsqlserver2005/1846/
DECLARE @tableA table (itemCode NVARCHAR(3), itemName NVARCHAR(100), itemGroup NVARCHAR(3))
DECLARE @itemCode varchar(10)
SET @itemCode = 'SPZ' 
INSERT INTO @tableA
SELECT 'SFD', 'Softdrink', NULL
UNION ALL SELECT 'CCL', 'Coca Cola', 'SFD'
UNION ALL SELECT 'FNT', 'Fanta', 'SFD'
UNION ALL SELECT 'SPR', 'Sprite', 'SFD'
UNION ALL SELECT 'ACL', 'Alcohol', NULL
UNION ALL SELECT 'TQL', 'Tequila', 'ACL'
UNION ALL SELECT 'VDK', 'Vodka', 'ACL'
UNION ALL SELECT 'SMN', 'Smirnoff', 'VDK'
UNION ALL SELECT 'SPZ', 'Sprite Zero', 'SPR'
 
-- recursion using common table expression
;WITH cte AS (
	SELECT itemCode, itemName, itemGroup
	, 1 AS itemDepth
	FROM @tableA -- replace with your table name
	WHERE itemCode = @itemCode
	UNION ALL
	SELECT t.itemCode, t.itemName, t.itemGroup
	, c.itemDepth + 1 AS itemDepth
	FROM @tableA t
	INNER JOIN cte c ON c.itemGroup = t.itemCode) 
SELECT itemCode,itemName,itemGroup FROM cte 
 
---------------------------------------------------------------------------------
itemCode	itemName	itemGroup
SPZ	Sprite Zero	SPR
SPR	Sprite	SFD
SFD	Softdrink	NULL

Open in new window

emi_sastra

ASKER

Hi louislietaer,

Thanks for the link, I will learn it later.

emi_sastra

ASKER

Hi Sharath_123,

Certainly I will learn it.

It works, but I don't want the search ItemCode data appears.
Just below result.

---------------------------------------------------------------------------------
itemCode itemName itemGroup
SPR Sprite SFD
SFD Softdrink NULL

Thank you.

SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

emi_sastra

ASKER

Hi All,

Thank you very much for your help.