?
Solved

Get Child in hierarchy for Given Parent Value.

Posted on 2009-05-02
15
Medium Priority
?
337 Views
Last Modified: 2012-05-06
Hi,

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

How to get all the list of the child of SFD?

Thank you.
0
Comment
Question by:emi_sastra
  • 8
  • 4
  • 3
15 Comments
 
LVL 5

Expert Comment

by:louislietaer
ID: 24285400
select * from thetablename where itemgroup='SFD'

but I think the answer is too simple to be good ;)
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24285415
Hi louislietaer,

Yes, it just not that simple.

WTR        Water
SFD             Softdrink     WTR
CCL            Coca Cola    SFD            
FNT             Fanta           SFD            
SPR             Sprite          SFD            
SPRZ             Sprite Zero          SPR
ACL             Alcohol
TQL             Tequila        ACL
VDK             Vodka         ACL

How about it?

Thank you.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24285472
What is your expected result for the above sample set?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 5

Expert Comment

by:louislietaer
ID: 24285478
You will have to a StoredProcedure

Procedure LookForParents (in id integer, inout reponse string)
begin
   reponse = reponse + ', ' + (SELECT ItemName
                               FROM thetablename
                               WHERE ItemCode  = ID)
if id = 0
then
   return
else
   id = (SELECT ItemGroup
         FROM thetableName
         WHERE ItemCode = ID)
end
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24285501
Hi Sharat_123,

ItemCode   ItemName   ItemGroup
SFD             Softdrink    
CCL            Coca Cola    SFD            
FNT             Fanta           SFD            
SPR             Sprite          SFD          
SPRZ           Sprice Zero  SPR  
ACL             Alcohol
TQL             Tequila        ACL
VDK             Vodka         ACL

If I provide "SFD" then the output will be:

ItemCode   ItemName   ItemGroup
CCL            Coca Cola    SFD            
FNT             Fanta           SFD            
SPR             Sprite          SFD        
SPRZ           Sprice Zero  SPR

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24285507
Hi louislietaer,

Again, it just not that simple.

Thank you.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 24285529
try this
DECLARE @tableA table (itemCode NVARCHAR(3), itemName NVARCHAR(100), itemGroup NVARCHAR(3))
DECLARE @itemCode varchar(10)
SET @itemCode = 'SFD' 
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 itemGroup = @itemCode
	UNION ALL
	SELECT t.itemCode, t.itemName, t.itemGroup
	, c.itemDepth + 1 AS itemDepth
	FROM @tableA t
	INNER JOIN cte c ON t.itemGroup = c.itemCode) 
SELECT itemCode,itemName,itemGroup FROM cte 

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24285535
Hi Sharat_123,

How to find certain ItemCode childs?

Thank you.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24285540
Did you try that? Certain itemcode is passed with this declaration and used the same inside the CTE also.
SET @itemCode = 'SFD'  
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24285556
Message:

Must declare the scalar variable @itemCode

Must declare the Table variable @TableA

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24285560
Sorry, get it.

Where is the code that find 'SFD' ?

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24285563
I get it.

WHERE itemGroup = @itemCode

Just this ?

Thank you.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24285564
please execute the complete code.
DECLARE @tableA table (itemCode NVARCHAR(3), itemName NVARCHAR(100), itemGroup NVARCHAR(3))
DECLARE @itemCode varchar(10)
SET @itemCode = 'SFD' 
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 itemGroup = @itemCode
	UNION ALL
	SELECT t.itemCode, t.itemName, t.itemGroup
	, c.itemDepth + 1 AS itemDepth
	FROM @tableA t
	INNER JOIN cte c ON t.itemGroup = c.itemCode) 
SELECT itemCode,itemName,itemGroup FROM cte 
 
itemCode	itemName	itemGroup
CCL	Coca Cola	   SFD
FNT	Fanta	    SFD
SPR	Sprite	    SFD
SPZ	Sprite Zero	SPR

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24285579
Hi Sharat_123,

I get it.

Thank you very much for your help.
0
 
LVL 5

Expert Comment

by:louislietaer
ID: 24285752
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question