SQL Query resursive - get parent records

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

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

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.

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

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

Oops.  Just to be clear, that first sentence was a question.

 What makes the first 4 records "related parents"?

 James

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

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

Hi,

No actually there isnt ,, maybe for one record there will be 1 parent or 10 parent