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.
LVL 1
emi_sastraAsked:
Who is Participating?
 
louislietaerCommented:
select * from thetablename where Itemcode=(select ItemGroup from thetablename where ItemCode='SPZ')
0
 
emi_sastraAuthor Commented:
Hi louislietaer,

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

Thank you.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
SharathData EngineerCommented:
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

0
 
emi_sastraAuthor Commented:
Hi louislietaer,

Thanks for the link, I will learn it later.


0
 
emi_sastraAuthor Commented:
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.
0
 
SharathData EngineerCommented:
try like this
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 where itemCode <> @itemCode
 
------------------------------------------------------------------------------------------
 
itemCode	itemName	itemGroup
SPR	Sprite	SFD
SFD	Softdrink	NULL

Open in new window

0
 
emi_sastraAuthor Commented:
Hi All,

Thank you very much for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.