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.
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.
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.
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
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
ASKER
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.
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.
ASKER
Hi louislietaer,
Again, it just not that simple.
Thank you.
Again, it just not that simple.
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Sharat_123,
How to find certain ItemCode childs?
Thank you.
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'
SET @itemCode = 'SFD'
ASKER
Message:
Must declare the scalar variable @itemCode
Must declare the Table variable @TableA
Thank you.
Must declare the scalar variable @itemCode
Must declare the Table variable @TableA
Thank you.
ASKER
Sorry, get it.
Where is the code that find 'SFD' ?
Thank you.
Where is the code that find 'SFD' ?
Thank you.
ASKER
I get it.
WHERE itemGroup = @itemCode
Just this ?
Thank you.
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
ASKER
Hi Sharat_123,
I get it.
Thank you very much for your help.
I get it.
Thank you very much for your help.
You will find a nice article on slq recursive requests
http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/recursivequeriesinsql1999andsqlserver2005/1846/
http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/recursivequeriesinsql1999andsqlserver2005/1846/
but I think the answer is too simple to be good ;)