T-SQL for parent node - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

T-SQL for parent node

Author

Topic

mariyaqw
Starting Member

7 Posts

Posted - 2010-10-20 : 05:58:12

I have a table which stores data in the binary tree structure something like

Userid Parentid

001 null
002 001
003 001
004 002
005 002
006 003
007 003

Now, my problem is how the Parentid get inserted automatically for a Userid?

something like

when i input 002 as Userid,i want 001 to get inserted automatically in the Parentid column

when i input 004 as Userid,002 should get inserted automatically in the table as Parentid

please help me..its urgent

regards,
mariyaqw

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-10-20 : 06:21:06

How do you know which is the parent?

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

mariyaqw
Starting Member

7 Posts

Posted - 2010-10-20 : 06:34:13

Since the table stores data in a binary tree format,001 is the root and has no parent.
But when 002 is inserted,it will be the first child of 001 and 003 when inserted will be the second child of 001.

Then,when 004 is inserted,it should be again the first child of 002

How will i implement this?

regards,
mariyaqw

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-10-20 : 08:22:42

[code]DECLARE @UserID int, @ParentID int
SET @UserID = 15
SET @ParentID = (@UserID - (@UserID % 2)) / 2[/code]

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-10-20 : 08:29:39

The above solution requires the hierarchy to look like this:

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-10-21 : 06:34:23

Did it work?

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

mariyaqw
Starting Member

7 Posts

Posted - 2010-10-21 : 07:00:35

Hi Lumbago,

Thanks a lot for your reply.

But in my table Userid column is IDENTITY.

regards,
mariyaqw

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-10-21 : 08:08:01

Then you have two options
- either make a trigger to update the ParentID with the code I gave you
- create ParentID as a persisted computed column:

CREATE TABLE myTable (
  UserID int NOT NULL IDENTITY (1, 1),
  ParentID  AS (UserID - (UserID % 2)) / 2 PERSISTED 
)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

mariyaqw
Starting Member

7 Posts

Posted - 2010-10-22 : 01:47:47

Hi Lumbago,

Thank you so much.Its working..

If you don't mind,i have some more doubts like

if i add 2 more columns to my table as 'Usertype and Dealerid',

then how the Parentid can be calculated automatically?

If i need a table structure in this way like

Usertype: 0 for admin

1 for dealer

2 for customer

Userid column is IDENTITY.And the same field is used for 3 Usertypes.

Dealer is also a Customer.

If Usertype is 0 or 1,then parentid should be null.

The Userid column value 001 will be for Usertype 0 only and this will be static as this is for administrator.

The Administrator will add the Dealers and the Dealers will add the customers.

So, the sequence of whose Userid (Dealers or Customers) will be 002 or 003 or the rest is unpredictable as this would depends on who will log in(Admin or Dealer) and add customers.

Userid Parentid Usertype Dealerid

001 null 0 null

002 null 1 002

003 002 2 002

004 null 1 004

005 002 2 002

006 004 2 004

007 003 2 002

The hierarchical structure for the above given table will look like

002 004
/ \ / \
003 005 006
/ \ / 002

regards,
mariyaqw

mariyaqw
Starting Member

7 Posts

Posted - 2010-10-22 : 01:51:03

002
/ 003 005
/ \ / 002

004
/ 006

regards,
mariyaqw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources