T-SQL QUERY PROBLEM

I have table called Summary and the data in the table looks like this:

ID     Type    Name         Parent

1      Act     Rent          Null
2      Eng     E21-01-Rent   Rent
3      Prj     P01-12-Rent   E21-01-Rent
1      Act     Fin           Null
2      Eng     E13-27-Fin    Fin
3      Prj     P56-35-Fin    E13-27-Fin

I am writing a SP which has to pull the parent based on type. Here always the type Act has ID 1, Eng has ID 2 and Prj has ID 3.

The type ACT 开发者_开发问答parent is always NUll, type Eng parent is Act and type Prj parent is Eng

Now I have table called Detail.I am writing a SP to insert Detail Table data to the Summary table.

I am passing the id as parameter:

I am having problem with the parent. How do I get that?

I can always say when ID is 1 then parent is Null but when ID is 2 then parent is name of ID 1 similarly when ID is 3 then parent is name of ID2.

How do I get that?

Can anyone help me with this:

I advise re-visiting your data model, but maybe this will help...

/* @Parent is VarChar(255) because definition is unknown.
   We use Top(1) because in example ID is not unique.
   We assume the ID being passed in is declared as @ID.*/
Declare @ParentID Int
Set @ParentID=(@ID-1)
Declare @Parent VarChar(255)
Set @Parent = (Select Top(1) Name From Summary
               Where ID=@ParentID) 

Just insert @Parent into the Parent column.

I think you must have a primary key (another ID which is unique) because with this model you will always have the first row of the type for example prj will always have the parent "E21-01-Rent" and no way to reach "E13-27-Fin" .

At last try this query

Select id,name,(Select Top 1 [Name] From [Summary]
               Where ID=@ID -1) as parent2
 from [Summary]
where id = @ID