Child and parent Relation

Question

Post reply

Child and parent Relation

ningaraju.n

SSCrazy

Points: 2322
More actions
August 9, 2010 at 10:22 pm

#103967

I have a table like this
Id ParentId
1 NULL
2 NULL
3NULL
41
51
62
72
83
93
I need the ordering in result set like this
IdParentId
1NULL
41
51
2 NULL
62
72
3NULL
83
93
Please do the favour

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Sorry difficult play :-P, but desperate to see the reply

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

I think this does what you need.

--test table

CREATE TABLE OrderTest

(id INT,

parentid INT NULL)

--test data

INSERT INTO OrderTest

VALUES

(1, NULL),

(2, NULL),

(3, NULL),

(4, 1),

(5, 1),

(6, 2),

(7, 2),

(8, 3),

(9, 3)

--query

SELECT *

FROM OrderTest

ORDER BY COALESCE(ParentId,Id), Id

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Grant Fritchey (8/10/2010)
I think this does what you need.
--test table
CREATE TABLE OrderTest
(id INT,
parentid INT NULL)
--test data
INSERT INTO OrderTest
VALUES
(1, NULL),
(2, NULL),
(3, NULL),
(4, 1),
(5, 1),
(6, 2),
(7, 2),
(8, 3),
(9, 3)
--query
SELECT *
FROM OrderTest
ORDER BY COALESCE(ParentId,Id), Id

Ahh 🙂 grant .AWESOME play with COALESCE function.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Probably would have worked with ISNULL too, but COALESCE came into my head first.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Yes its working with ISNULL

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ningaraju.n SSCrazy Points: 2322 More actions · Answer 6

Thanks Bhuvanesh... I am glad to you for helping out me

ningaraju.n SSCrazy Points: 2322 More actions · Answer 7

Thanks Fritchey... I am glad to you for helping out me

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

CELKO (8/11/2010)
You might want to look at the Nested Sets model for this. Life will be better.

Any example or link ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 9

It seems to me that this solution will only work if there are only two levels of parent/child relationship. Do you have cases where the relationships go to three or more levels (i.e. 10's parent is 4, 11's parent is 10, etc)?

brindaam SSC Rookie Points: 28 More actions · Answer 10

Hi,

I have two queries, say for example

Select distinct 0 parent_id, ROW_NUMBER() OVER(

ORDER BY Column2_Desc ) AS 'ID', Column2_Desc

From table1

Union

Select distinct 0 parent_id, ROW_NUMBER() OVER(

ORDER BY Column2_Desc ) AS 'ID', Column2_Desc

From table2

I don’t have relationship between two tables.But I want to frame parent child relationship between these two so that my resultset should like

Parent_ididColumn2_Desc

01ABC

02XYZ

03ASDF

14VGTY

25DFT

I don’t want to use stored procedure.

Kindly Help Me Out

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 11

brindaam (12/13/2010)
Hi,
I have two queries, say for example
Select distinct 0 parent_id, ROW_NUMBER() OVER(
ORDER BY Column2_Desc ) AS 'ID', Column2_Desc
From table1
Union
Select distinct 0 parent_id, ROW_NUMBER() OVER(
ORDER BY Column2_Desc ) AS 'ID', Column2_Desc
From table2
I don’t have relationship between two tables.But I want to frame parent child relationship between these two so that my resultset should like
Parent_ididColumn2_Desc
01ABC
02XYZ
03ASDF
14VGTY
25DFT
I don’t want to use stored procedure.
Kindly Help Me Out

it seems another/new query.please start these with NEW POST so that you can get better results ans people dont get confused with post's heading and internal stuff like this

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

harinerella Mr or Mrs. 500 Points: 548 More actions · Answer 12

CREATE TABLE OrderTest

(id INT,

parentid INT NULL)

--test data

INSERT INTO OrderTest VALUES (1, NULL)

INSERT INTO OrderTest VALUES (2, NULL)

INSERT INTO OrderTest VALUES (3, NULL)

INSERT INTO OrderTest VALUES (4, 1)

INSERT INTO OrderTest VALUES (5, 1)

INSERT INTO OrderTest VALUES (6, 2)

INSERT INTO OrderTest VALUES (7, 2)

INSERT INTO OrderTest VALUES (8, 3)

INSERT INTO OrderTest VALUES (9, 3)

INSERT INTO OrderTest VALUES (10,6)

WITH Hierarchy (Parentid,ID, lvl,sort) AS

(SELECT ParentiD, id,0, cast(isnull( id,0) as varchar(1024))

FROM OrderTest

WHERE ParentiD IS null

UNION ALL

SELECT ot.ParentiD, ot.ID, lvl+1,cast(sort+'|'+cast(lvl as varchar(5))+'|'+cast(ot.parentid as varchar(5)) as varchar(1024))

FROM OrderTest ot INNER JOIN hierarchy h ON ot.ParentiD = h.ID

)

select ParentiD,ID,lvl

from Hierarchy

order by sort

Try this i hope this is what you are expecting.