• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 816
  • Last Modified:

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.
0
emi_sastra
Asked:
emi_sastra
  • 4
  • 2
  • 2
2 Solutions
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now