Parent-children Listing
-
guys i have a table with a self-referencing relationship. I want to get the child records listed just below their parent. just like: id parent value --- ------ ------ 1 null node 1 4 1 node 1/1 6 4 node 1/1/1 5 1 node 1/2 7 1 node 1/3 2 null node 2 3 null node 3 8 3 node 3/1 any help;
Help people,so poeple can help you.
Include the parent id and the child id in each record and then include them as the first two fields to sort by. :)
select p.id
,c.id
, .....
from some_table p
,some_table c
where p.id = c.parent_id_field
sort by p.id, c.idChris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
Include the parent id and the child id in each record and then include them as the first two fields to sort by. :)
select p.id
,c.id
, .....
from some_table p
,some_table c
where p.id = c.parent_id_field
sort by p.id, c.idChris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
but sir, what about multi-level threads?
Help people,so poeple can help you.
-
but sir, what about multi-level threads?
Help people,so poeple can help you.
You will need to know how many levels deep you want to go and join the table for each level. You might also be able to use a view to help with that. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
You will need to know how many levels deep you want to go and join the table for each level. You might also be able to use a view to help with that. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
i can't tie a user hands. is there "Recursion" in sql server.
Help people,so poeple can help you.
-
guys i have a table with a self-referencing relationship. I want to get the child records listed just below their parent. just like: id parent value --- ------ ------ 1 null node 1 4 1 node 1/1 6 4 node 1/1/1 5 1 node 1/2 7 1 node 1/3 2 null node 2 3 null node 3 8 3 node 3/1 any help;
Help people,so poeple can help you.
Hi, You could use a recursive query for this.
CTE(Common Table Expression) has the capability of handling unkown amount of recursions. For an example, seefor example http://msdn.microsoft.com/en-us/library/ms186243.aspx[^]. Best regards, mikaThe need to optimize rises from a bad design.My articles[^]
-
Hi, You could use a recursive query for this.
CTE(Common Table Expression) has the capability of handling unkown amount of recursions. For an example, seefor example http://msdn.microsoft.com/en-us/library/ms186243.aspx[^]. Best regards, mikaThe need to optimize rises from a bad design.My articles[^]
Over my head, mika; you are still the best. 100 :rose:
Help people,so poeple can help you.
-
i can't tie a user hands. is there "Recursion" in sql server.
Help people,so poeple can help you.
Ali Al Omairi(Abu AlHassan) wrote:
i can't tie a user hands.
Sometimes, I wish I could do that. :-D
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
Over my head, mika; you are still the best. 100 :rose:
Help people,so poeple can help you.
Thanks, but frankly I believe that this site has a bunch of real experts thus making CP so special. No other site I've come across has so positive and professional attitude at the same time as this.
The need to optimize rises from a bad design.My articles[^]
-
Over my head, mika; you are still the best. 100 :rose:
Help people,so poeple can help you.
CREATE TABLE #data
(
ID INT NOT NULL,
PID INT NULL,
VALUE VARCHAR(20) NOT NULL
);INSERT INTO #data
(ID, PID, VALUE)
VALUES
(1, NULL, 'node 1'),
(4, 1, 'node 1/1'),
(6, 4, 'node 1/1/1'),
(5, 1, 'node 1/2'),
(7, 1, 'node 1/3'),
(2, NULL, 'node 2'),
(3, NULL, 'node 3'),
(8, 3, 'node 3/1');WITH MyCTE AS
(
SELECT ID, PID, VALUE, 0 AS [LEVEL]
FROM #data
WHERE PID IS NULL
UNION ALL
SELECT D.ID, D.PID, D.VALUE, [LEVEL] + 1
FROM MyCTE M
JOIN #data D
ON D.PID = M.ID
)
SELECT ID, PID, VALUE, [LEVEL]
FROM MyCTE;DROP TABLE #data;
-
CREATE TABLE #data
(
ID INT NOT NULL,
PID INT NULL,
VALUE VARCHAR(20) NOT NULL
);INSERT INTO #data
(ID, PID, VALUE)
VALUES
(1, NULL, 'node 1'),
(4, 1, 'node 1/1'),
(6, 4, 'node 1/1/1'),
(5, 1, 'node 1/2'),
(7, 1, 'node 1/3'),
(2, NULL, 'node 2'),
(3, NULL, 'node 3'),
(8, 3, 'node 3/1');WITH MyCTE AS
(
SELECT ID, PID, VALUE, 0 AS [LEVEL]
FROM #data
WHERE PID IS NULL
UNION ALL
SELECT D.ID, D.PID, D.VALUE, [LEVEL] + 1
FROM MyCTE M
JOIN #data D
ON D.PID = M.ID
)
SELECT ID, PID, VALUE, [LEVEL]
FROM MyCTE;DROP TABLE #data;
thank you Russell. ;) 100 :rose: