March 15, 2010 at 9:05 pm
hi all,
the following is my table structure:
page_id(int,pk),parent_id(int),page_title(varchar 50) and the sample data
1, 0, Home
2,1, Park
3,1,Library
4,3,Library2
5,3,Library3
how do i find a record's 'parent', and then its parents up to 'Home' given a page_id? for example:
page_id=5 result:
3,1,Library
1,0,Home
I hope I've been clear enough, thanks for all help.
March 16, 2010 at 12:45 am
This should help..
CREATE TABLE TestTable
(
page_idINT,
parent_idINT,
page_titleVARCHAR(50)
)
DECLARE@strSQL VARCHAR(MAX)
DECLARE@iPageID INT
SET@iPageID = 5
INSERT TestTable
SELECT 1, 0, 'Home' UNION ALL
SELECT 2, 1, 'Park' UNION ALL
SELECT 3, 1, 'Library' UNION ALL
SELECT 4, 3, 'Library2' UNION ALL
SELECT 5, 3, 'Library3'
; WITH cteTableDetails AS
(
SELECTpage_id, parent_id,
CAST( page_id AS VARCHAR(MAX) ) PageIDPath,
CAST( page_title AS VARCHAR(MAX) ) PageTitlePath
FROMTestTable
WHEREparent_id = 0
UNION ALL
SELECTT.page_id, T.parent_id,
RT.PageIDPath + ',' + CAST( T.page_id AS VARCHAR(MAX) ),
RT.PageTitlePath + ',' + T.page_title
FROMTestTable T
INNER JOIN cteTableDetails RT
ON T.parent_id = RT.page_id
)
SELECT@strSQL = PageIDPath
FROMcteTableDetails
WHEREpage_id = @iPageID
SET@strSQL = ' SELECT * FROM TestTable WHERE page_id IN ( ' + @strSQL + ' ) '
EXECUTE( @strSQL )
You can also re-write the above code by replacing the Dynamic SQL with an implementation of String Splitting Function. I tried searching for a String Splitting Function but could not find it.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 16, 2010 at 1:22 am
Thanks Kingston,
Its working great! Thanks a million 🙂
March 16, 2010 at 1:37 am
Glad that i could help you out:-)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply