sql tree question - 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

General SQL Server Forums

New to SQL Server Programming

sql tree question

Author

Topic

zubair
Yak Posting Veteran

67 Posts

Posted - 2013-09-02 : 10:05:37

Hi,

I'm having difficulty writing a sql query where i need to get the parent records for a record all in the same table

Example:

ID PARTOF TITLE DESCRIPTION
====================================================
1 0 HOME HOME PAGE
2 1 SERVICES SERVICE PAGE
3 1 ABOUT US ABOUT US
4 3 ABOUT US:CONTACT CONTACT US
5 4 ABOUT US:CONTACT:MAP MAP

SO FOR EXAMPLE FOR RECORD ID 5 THE QUERY WILL GET ME ALL THE PARENT PAGES RELATED TO IT. IT WILL GET ID 4 AND 3

Can anyone help me with this query? any help appreciated.
Many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-02 : 10:19:37

do you need just immediate parent?
if yes, this should be enough


SELECT *
FROM Table t1
INNER JOIN Table t2
ON t2.ID = t1.PARTOF

if you need all parents until first level, use


;With Tree
AS
(
SELECT ID,PARTOF,TITLE,DESCRIPTION
FROM Table
WHERE ID = @ID

UNION ALL

SELECT t.ID,t.PARTOF,t.TITLE,t.DECSRIPTION
FROM Table t
INNER JOIN Tree tr
ON tr.PARTOF = t.ID
)

SELECT *
FROM Tree

OPTION(MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources