Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I need help in sql recursive query, for example purpose i m providing sample table with insert script.


SQL
CREATE   TABLE Details(
parentid varchar(10), DetailComponent varchar(10) , DetailLevel int)
GO

INSERT INTO Details 
SELECT '','7419-01',0 union all
SELECT '7419-01','44342-00',1 union all
SELECT '7419-01','45342-00',1 union all
SELECT '7419-01','46342-00',1 union all
SELECT '7419-01','47342-00',1 union all
SELECT '7419-01','48342-00',1 union all
SELECT '7419-01','49342-00',1 union all
SELECT '7419-01','50342-00',1 union all
SELECT '50342-00','51342-00',2 union all
SELECT '7419-01','52342-00',1 union all
SELECT '52342-00','54342-00',2 union all
SELECT '54342-00','54442-00',3 union all
SELECT '54342-00','54552-00',3 union all
SELECT '54552-00','R34S-54',4 union all
SELECT '54552-00','R123-54',4 union all
SELECT '54552-00','R111-54',4 union all
SELECT 'R111-54','R222-54',5 union all
SELECT 'R222-54','52342-00',6 union all
SELECT '7419-01','TEST34-00',1 union all
SELECT 'TEST34-00','445334-00',2 union all
SELECT '445334-00','52342-00',3  union all
SELECT '7419-01','1111-00',1 union all
SELECT '7419-01','1111-00',1 union all
SELECT '1111-00','52342-00',2 
GO


From the above table data i want a search query , for example if I search data with "52342-00" I want output to be below format using CTE.

NULL,'7419-01',0
'7419-01','52342-00',1
'7419-01','52342-00',1
'52342-00','54342-00',2
'54342-00','54552-00',3
'54552-00','R111-54',4
'R111-54','R222-54',5
'R222-54','52342-00',6

thanks
Posted
Comments
Andy Lanng 14-Sep-15 10:29am    
A: Is there an upper limit to the parent-child chain? I just want to make sure that loops are not possible or they will break the cte
B: Row 1 Will not be "NULL" because the source data is not NULL: NULL != ''
Member 11983027 14-Sep-15 10:39am    
Hi Andy Lanng,
A. Upto 10 levels relation is max.
B.Row 1 will be null means that is the top level record, it will stop there.
jaket-cp 14-Sep-15 11:08am    
Just to confirm is the parent, child relationship between DetailComponent and ParentId?
Also can you check you data - is the
union all SELECT 'R222-54','52342-00',6
correct?
Member 11983027 14-Sep-15 11:19am    
yes, relationship between is DetailComponent and ParentId.
jaket-cp 14-Sep-15 11:24am    
Okay if that is the case, I believe the input data may be incorrect.
I have written a CTE for the child-parent relationship but there are quite a few duplicates and the recursion does not stop, so I had to put a limit in.

You should use the 'Reply', so the user who wrote the comment will get a notification.

This is not a solution in itself, as the comment is not really adequate.

Here is a recursive cte for the parent child relationship.
SQL
with DetailData as (
	SELECT
		'' ParentID,
		'7419-01' DetailComponent,
		0 DetailLevel
	union all
	SELECT '7419-01','44342-00',1 union all
	SELECT '7419-01','45342-00',1 union all
	SELECT '7419-01','46342-00',1 union all
	SELECT '7419-01','47342-00',1 union all
	SELECT '7419-01','48342-00',1 union all
	SELECT '7419-01','49342-00',1 union all
	SELECT '7419-01','50342-00',1 union all
	SELECT '50342-00','51342-00',2 union all
	SELECT '7419-01','52342-00',1 union all
	SELECT '52342-00','54342-00',2 union all
	SELECT '54342-00','54442-00',3 union all
	SELECT '54342-00','54552-00',3 union all
	SELECT '54552-00','R34S-54',4 union all
	SELECT '54552-00','R123-54',4 union all
	SELECT '54552-00','R111-54',4 union all
	SELECT 'R111-54','R222-54',5 union all
	SELECT 'R222-54','52342-00',6 union all
	SELECT '7419-01','TEST34-00',1 union all
	SELECT 'TEST34-00','445334-00',2 union all
	SELECT '445334-00','52342-00',3  union all
	SELECT '7419-01','1111-00',1 union all
	SELECT '7419-01','1111-00',1 union all
	SELECT '1111-00','52342-00',2
), DetailDataRecursive as (
	select 
		ParentID,
		DetailComponent,
		DetailLevel
		,
		convert(varchar(max), DetailComponent) NameSpaceDetailComponent,
		convert(varchar(max), row_number() over (partition by ParentID order by DetailComponent)) SortCode,
		1 level
	from DetailData
	where ParentID = ''

	union all
	select 
		DetailDataChild.ParentID,
		DetailDataChild.DetailComponent,
		DetailDataChild.DetailLevel
		,
		DetailDataParent.NameSpaceDetailComponent + '.' + DetailDataChild.DetailComponent NameSpaceDetailComponent,
		DetailDataParent.SortCode + '.' + convert(varchar(max), format(row_number() over (partition by DetailDataChild.ParentID order by DetailDataChild.DetailComponent), 'd4')),
		level + 1 level

	from DetailData DetailDataChild
	inner join DetailDataRecursive DetailDataParent
		on DetailDataChild.ParentID = DetailDataParent.DetailComponent
	--a limit has been implemented here as otherwise too many recursions
	where DetailDataParent.level <= 10
)
select * from DetailDataRecursive
order by SortCode
;

From this query you should be able to see the parent child relationships in the NameSpaceDetailComponent column.

If this is correct and your expected result.
Then make a comment and I may be able to help some more.
 
Share this answer
 
v2
Not sure if I understand the question correctly, but if you need to find the parent components based on the given component id, perhaps something like this:

SQL
WITH BOM (ParentComponent, ChildComponent, DetailLevel) AS (
    SELECT a.parentid AS ParentComponent, 
	       a.DetailComponent AS ChildComponent,
           a.DetailLevel AS DetailLevel
    FROM Details a
	WHERE a.DetailComponent = '52342-00' -- defines the most detailed row
    UNION ALL
    SELECT a.parentid AS ParentComponent, 
	       a.DetailComponent AS ChildComponent,
           a.DetailLevel AS DetailLevel
    FROM Details a INNER JOIN BOM AS b
        ON b.ParentComponent = a.DetailComponent AND b.DetailLevel - 1 = a.DetailLevel
        -- move to parent which is one level above
)
SELECT DISTINCT b.ParentComponent, b.ChildComponent, b.DetailLevel
FROM BOM b
ORDER BY b.DetailLevel, b.ParentComponent, b.ChildComponent
 
Share this answer
 
Hi,

Check this....

Recursion in SQL using CTE[^]

hope this will help you.


Cheers
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900