Link to home
Start Free TrialLog in
Avatar of emi_sastra
emi_sastra

asked on

Get Child in hierarchy for Given Parent Value.

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.
Avatar of Louis LIETAER
Louis LIETAER
Flag of France image

select * from thetablename where itemgroup='SFD'

but I think the answer is too simple to be good ;)
Avatar of emi_sastra
emi_sastra

ASKER

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.
What is your expected result for the above sample set?
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
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.
Hi louislietaer,

Again, it just not that simple.

Thank you.
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Sharat_123,

How to find certain ItemCode childs?

Thank you.
Did you try that? Certain itemcode is passed with this declaration and used the same inside the CTE also.
SET @itemCode = 'SFD'  
Message:

Must declare the scalar variable @itemCode

Must declare the Table variable @TableA

Thank you.
Sorry, get it.

Where is the code that find 'SFD' ?

Thank you.
I get it.

WHERE itemGroup = @itemCode

Just this ?

Thank you.
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

Hi Sharat_123,

I get it.

Thank you very much for your help.