id      details      parent
1       this         null
2       is           1
3       a sample     2
4       table        3

quote:

---

Originally posted by MIK_2008

If @param=4
Begin
Select * from TableName where ID=1
End


Is the above what you are looking for? If not then what do you mean by "if the param is 4 how will i get the 1 row? thanks guys in advanced! more power!" Can you explain the logic behind your statement?

---

quote:

---

Originally posted by MIK_2008

If @param=4
Begin
Select * from TableName where ID=1
End


Is the above what you are looking for? If not then what do you mean by "if the param is 4 how will i get the 1 row? thanks guys in advanced! more power!" Can you explain the logic behind your statement?

---

declare @t table (id int ,parent int)
insert @t
select 1,null union
select 2,1 union
select 3 ,2 union
select 4,3

select * from @t

;with cte
as
(

select id,parent,parent p from @t 
union all
select t1.id,t2.id,t2.p from @t t1 inner join cte t2 on t1.parent=t2.id

)

select min(p)parent, id from cte where id=4 group by id

quote:

---

Originally posted by Sachin.Nand

This?

declare @t table (id int ,parent int)
insert @t
select 1,null union
select 2,1 union
select 3 ,2 union
select 4,3

select * from @t

;with cte
as
(

select id,parent,parent p from @t 
union all
select t1.id,t2.id,t2.p from @t t1 inner join cte t2 on t1.parent=t2.id

)

select min(p)parent, id from cte where id=4 group by id

PBUH

---

Table1
id      details      
1       this         
2       is           
3       a sample     
4       table        

Table2
id      parent
2       1
3       2
4       3