SQL Query resursive - get parent records

Hi All,

consider sql table have following columns - records,

id         name         parent_id
1          mem          2
2          mem          3
3          mem          4
4          mem          0
5         sel              6
6         sel              0

i want to get all related parents for record id=1 means ,, in one query i want to get records 1,2,3,4 but note 5-6 because they are not related to record 1 ..






LVL 7
Abu HamdanEnterprise Architect, PM ExpertAsked:
Who is Participating?
 
answer_meConnect With a Mentor Commented:
If there is any max limit of number of parent then its possible ....
0
 
answer_meCommented:
begin
declare @temptable table
(
id  int,
name  nvarchar(200),
parent int
)
declare @searchid int
set @searchid =<yourid>

while (1=1)
begin
insert into @temptable
select * from yourtable where id =@searchid
if @@rowcount=0
break
select top 1 @searchid=parent from @temptable where parent =@searchid
end
select * from @temptable
end
0
 
Chris MangusDatabase AdministratorCommented:
More realistically...the data would look like this:

id         name         parent_id
1          mem          0
2          mem          1
3          mem          2
4          mem          3
5         sel              6
6         sel              0

And a query to get the relationships would be:

SELECT    P1.[ID], P2.[ID]
FROM         dbo.myTable AS P1
INNER JOIN dbo.myTable AS P2 ON P1.[id] = P2.parent_id
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Chris MangusDatabase AdministratorCommented:
Oops...

More realistically...the data would look like this:

id         name         parent_id
1          mem          0
2          mem          1
3          mem          1
4          mem          1
5         sel              6
6         sel              0

And a query to get the relationships would be:

SELECT    P1.[ID], P2.[ID]
FROM         dbo.myTable AS P1
INNER JOIN dbo.myTable AS P2 ON P1.[id] = P2.parent_id

In your sample data, records 3 and 4 don't necessarily relate to record 1, unless you have a multi-level heirarchy and not just a 2 level heirarchy.
0
 
Chris MangusDatabase AdministratorCommented:
If your data does have more than 2 heirarchies, and is like you originally posted, the following query will pull it out:

SELECT  P1.[ID] As Parent, P2.[ID] As Child1, P3.[ID] As Child2
FROM dbo.myTable AS P1
LEFT OUTER JOIN dbo.myTable AS P2 ON P1.[ID] = P2.Parent_ID
LEFT OUTER JOIN dbo.myTable AS P3 ON P2.[ID] = P3.Parent_ID
Go
0
 
James0628Commented:
What makes the first 4 records "related parents".  In your example, it seems to be that name is "mem".  If you're looking for all records with the same name as id 1, something like the following should work:

select * from table
where name = (select name from table where id = 1)


 James
0
 
James0628Commented:
Oops.  Just to be clear, that first sentence was a question.

 What makes the first 4 records "related parents"?

 James
0
 
Abu HamdanEnterprise Architect, PM ExpertAuthor Commented:
Hello,,

Its mutiple level heirarchy ,, i mean that there is record id=1 ,, and record one has parent id as record 2 ,,, then record 2 has parent id as 3 ,, then 3 has parent id as 4 ,, then 4 has no parent id ,, then just get these 4 records .. and discard other ,, and the query should be something like this:

select (all -level-parents) from table where id=1 ,,

and result as

1          mem          2
2          mem          3
3          mem          4
4          mem          0

-----

and if i want parents for record 5 same query but result should be as:

5         sel              6
6         sel              0

Also i know how to do it as stored procs -- but i am looking to do as query if possible






0
 
James0628Commented:
Ah.  Then I guess you're going to have to look at some of the other suggestions.  Is there any limit on how many levels you can have?  That will probably be a key factor.

 James
0
 
Abu HamdanEnterprise Architect, PM ExpertAuthor Commented:
Hi,

No actually there isnt ,, maybe for one record there will be 1 parent or 10 parent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.