find out tree structure - 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)

find out tree structure

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-09-17 : 01:05:12

Hi

I have this query


SELECT     dbo.Article.ArticleId, dbo.Category.Name, dbo.Category.Parent, Category_1.Name AS Expr1, dbo.Category.CategoryId
FROM         dbo.Article INNER JOIN
                      dbo.ArticlesInCategories ON dbo.Article.ArticleId = dbo.ArticlesInCategories.ArticleId INNER JOIN
                      dbo.Category ON dbo.ArticlesInCategories.CategoryId = dbo.Category.CategoryId INNER JOIN
                      dbo.Category AS Category_1 ON dbo.Category.Parent = Category_1.CategoryId
WHERE     (dbo.Article.ArticleId = 2387)

This query show in what category name a article is placed in. The thing is that if the dbo.Category.Parent column is empty or null it mean the article is placed in the top category (top node). But if the dbo.Category.Parent has a value it need to check all the categories, so that I can find out the tree structure where the article is placed in. Hope this make sence, can anyone help me out?

Sachin.Nand

2937 Posts

Posted - 2010-09-17 : 03:03:41

Post some sample data & expected o/p.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

PavanKK
Starting Member

32 Posts

Posted - 2010-09-17 : 03:42:15

Use CTE to get all recursive Parent Categories.

KK

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-09-17 : 03:55:52

If I run that query I get this result..


ArticleId	Name	Parent	      CategoryId
2387	       History	Divisional	406

H

I would like the result to be ...



ArticleId	CombinedCategoriesPath
2387	         History\Divisional

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-17 : 04:18:06

Long time no see!
Here is a starter for you, aka Recursive CTE.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=150126

N 56°04'39.26"
E 12°55'05.63"

Sachin.Nand

2937 Posts

Posted - 2010-09-17 : 04:31:58

quote:
Originally posted by Peso

Long time no see!
Here is a starter for you, aka Recursive CTE.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=150126

N 56°04'39.26"
E 12°55'05.63"

That has to be one of the most easiest example of Recursive CTE

.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

PavanKK
Starting Member

32 Posts

Posted - 2010-09-17 : 04:49:28

try this

;WITH Cte AS
(
SELECT dbo.Category.CategoryId,dbo.Category.Name, dbo.Category.Parent,1 Level
FROM dbo.Article
INNER JOIN dbo.ArticlesInCategories ON dbo.Article.ArticleId = dbo.ArticlesInCategories.ArticleId
INNER JOIN dbo.Category ON dbo.ArticlesInCategories.CategoryId = dbo.Category.CategoryId
WHERE (dbo.Article.ArticleId = 2387)

UNION ALL

SELECT dbo.Category.CategoryId,dbo.Category.Name, dbo.Category.Parent,Level+1
FROM dbo.Category
JOIN Cte ON dbo.Category.CategoryId = Cte.Parent
)

SELECT 2387 AS ArticleId, STUFF((SELECT '\'+Name FROM Cte ORDER BY Level FOR XML PATH('')),1,1,'') AS CombinedCategoriesPath

KK

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-09-17 : 05:12:57

Hi KK

That worked just as I needed it to do, Thanks a million!

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-17 : 12:26:00

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

Your design has errors. There is no such thing as a "category_id", since both of those are attribute properties. You can have a "<something>_category" or "<something>_id" however.

It looks like you are using an adjacency list model for a hierarchy. Switch over to the nested set model and you will not have any problems.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources