T-SQL QUERY PROBLEM

Question

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:

So how are parents related in your data then? Or if they are not and it is only through the rule you describe in the question then how do you know which 3 elements are grouped together? — Martin Smith
– Martin Smith, Commented Dec 31, 2010 at 21:37
Martin The act type name is always appended to the rest of the types name. I have edited the sample data in the Summary table. SO that you can get an idea on how to group them together — user556674
– user556674, Commented Dec 31, 2010 at 21:43

NoAlias · Accepted Answer · 2010-12-31 22:26:13Z

1

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.

answered Dec 31, 2010 at 22:26

NoAlias

9,2032 gold badges30 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tareq · Accepted Answer · 2011-01-02 18:08:21Z

1

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

answered Jan 2, 2011 at 18:08

Tareq

1,42730 silver badges29 bronze badges

Collectives™ on Stack Overflow

T-SQL QUERY PROBLEM

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related