Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: More Trees & Hierarchies in SQL
 Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 7

anatoliyb
Starting Member

1 Posts

Posted - 11/25/2003 :  17:37:42  Show Profile  Reply with Quote
quote:
Originally posted by AskSQLTeam

Hierarchies are sometimes difficult to store in SQL tables...things like trees, threaded forums, org charts and the like...and it's usually even harder to retrieve the hierarchy once you do store it. Here's a method that's easy to understand and maintain, and gives you the full hierarchy (or any piece of it) very quickly and easily.<P>Article <a href="/item.asp?ItemID=8866">Link</a>.


Hi Rob,
I am creating a 6-level tree structure without any identation. I need just regular tree structured set of rows and columns. However when I have missing levels (like some parts of the tree don't have 3, 4, and 5 levels) a statement with recursive joins don't show those parts of the tree at all. Besides creating fake levels, are there some other ways to overcome this problem? Thank you.
Go to Top of Page

szelinak
Starting Member

1 Posts

Posted - 01/12/2004 :  15:54:01  Show Profile  Reply with Quote
Rob - I'm just joining the chorus eagerly waiting for part 2 of the article ...
Was able to apply part 1 immediately to a current project and saved the company some money in the process.
Please send a link to part 2 when available.
Thank-you.
Susan
Go to Top of Page

Skinman
Starting Member

USA
1 Posts

Posted - 02/15/2004 :  20:12:37  Show Profile  Reply with Quote
I really like the way this article is going, so...

Rob, please do part two. It is like a good novel I want to see how it ends.

Thanks.

Thanks,
Skinman
Go to Top of Page

aluminum
Starting Member

6 Posts

Posted - 03/23/2004 :  12:08:14  Show Profile  Reply with Quote
Yet another vote for part 2!
Go to Top of Page

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 03/24/2004 :  22:17:51  Show Profile  Reply with Quote
no news on Part II it seems, then again its been over 1 year!

Great work nonetheless!
Go to Top of Page

aluminum
Starting Member

6 Posts

Posted - 03/25/2004 :  10:03:26  Show Profile  Reply with Quote
OK, I actually have a question.

I've been looking at this and I think it's quite ingenious.

However, I'm struggling to figure out the best way to select siblings. The method seems Ideal for picking out trees and branches (proper terminology?) but I'm stuck on the sibling thing.

Specifically, I'm looking for a way to dynamically build out a multi-level navigation system. Example:

colors
- primary
- - red
- - blue
- - yello
- scondary
- - orange
- - purple
- - green
shapes
- geometric
- - square
- - triangle
- organic
- - blob
- - splotch

So, let's say I wanted to pull out the navigation for BLOB. It should look like this:

colors
shapes
- geometric
- organic
- - *blob*
- - splotch

Can I still do this with one call?

Go to Top of Page

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 03/27/2004 :  23:01:22  Show Profile  Reply with Quote
how to you get the parent node of any given node?
Go to Top of Page

aluminum
Starting Member

6 Posts

Posted - 03/31/2004 :  15:32:05  Show Profile  Reply with Quote
I can't tell if you're asking a rhetorical question or not in response to mine, but wouldn't the parent simply be Current Node's lft value - 1?

Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 04/28/2004 :  10:51:36  Show Profile  Reply with Quote
Maintaining Hierarchies

Jay White
{0}
Go to Top of Page

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 06/19/2004 :  23:03:46  Show Profile  Reply with Quote
Is there a query that can update the # of content items linked to each category?

Category#1 (25)
SubCategory#2 (10)
SubCategory#3 (15)
SubSubCategory#3.1 (10)
SubSubCategory#3.2 (5)


Is that possible? Realy stumped on this!

do people use a batch query for this or a on-the-fly type query?
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/20/2004 :  00:22:44  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
here's my version of tree structures...

http://www.seventhnight.com/treestructs.asp


to do the count query with my example:

Select
nodeId,
Cnt = (Select count(*) From @paths as B Inner Join @contentItems as C On B.cNodeId = C.nodeId Where path like '%' + right(@pad + convert(nvarchar,A.NodeId),len(@pad)) + ';%')
From @nodes

Where @contentItems has items and the nodeId they are linked to.




Corey
Go to Top of Page

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 06/20/2004 :  10:14:22  Show Profile  Reply with Quote
I've already implemented the tree structure of this threads article. Is yours the same?
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/20/2004 :  10:22:42  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
Its pretty close, biggest difference is that I use a seperate table to keep the relationships instead of on the same row.

In this example, I believe my query would work out too:
Select
nodeId,
Cnt = (Select count(*) From <treeTbl> as B Inner Join <contentForNode> as C On B.Node = C.Node Where path like '%/' + convert(nvarchar,A.NodeId) + '/%')
From <treeTbl> as A

You may also have to count any content items linked directly to that node as I don't think the lineage includes the current node.



Corey
Go to Top of Page

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 06/25/2004 :  11:51:44  Show Profile  Reply with Quote
Ok let me explain my case here a bit better.

I have my categories table, and I added a column to store the # of content items whithin each category (either the root category, or sub-category).
I have a contentscategory table that has the categoryID (or nodeID) and the contentID stored in it.

I need a BATCH query that will tally up all the content items under each category.
I have been struggling with this for a while so help on this would be really great!!!

Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/25/2004 :  12:09:48  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
Maybe I'm reading this wrong, but it sounds like you just want the total # content items that are directly linked to each category.
quote:
Select CategoryId, count(*) From contentscategory Group By CategoryId


If I don't have it right, start a new thread with the table layout, sample data, and desired result and we will get you squared away in no time.

Corey
Go to Top of Page

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 06/25/2004 :  13:08:22  Show Profile  Reply with Quote
Ok i'll start a new thread.

That won't work because the categoryID has a parent, and that parent might have a parent etc...to the root node. And the root node should have a total of all the content items associated with ALL its child nodes. And the child of the parent should have all the content items associated to it etc...

I'll try and start a new thread in a bit (have to head out for a bit!), but its the same as the article anyhow expect I have a content table and a contectCategories table (the category table being the main table in the article, i just renamed it).
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/25/2004 :  13:45:47  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
So then it would be like the post i gave earlier...

Follow the article instuctions to build the 'lineage'.

If lineage is in a table called categoryTree then

Select
nodeId,
Cnt = (Select count(*) From categoryTree as B Inner Join contentsCategory as C On B.Node = C.Node Where path like '%/' + convert(nvarchar,A.NodeId) + '/%')
From categoryTree as A


Corey
Go to Top of Page

aluminum
Starting Member

6 Posts

Posted - 09/30/2004 :  17:52:03  Show Profile  Reply with Quote
In Part 2 of this article I'll cover all the necessary tree operations that are needed (add, delete, move, promote)

Just curious to see if anyone found an alternative resource that covers the topics that were going to be discusses in the non-existant part 2...
Go to Top of Page

SalmanAhmed
Starting Member

Canada
5 Posts

Posted - 10/31/2004 :  22:47:03  Show Profile  Visit SalmanAhmed's Homepage  Reply with Quote
Hi,

I developed a directory link application using some of the ideas in this great article. (http://www.snadtech.com/demos/directory)
Just a quick question, at the moment whenever someone adds a new category or makes any updates they are required to manually re-build the category structure to ensure the bread crumbs are correct etc.

Should I just automatically rebuild upon any changes to avoid confusion for the user or put the responsibility on the user to do this?

Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/01/2004 :  07:10:07  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
I would either automatically rebuild, or you can update with the changes that you have made...

http://www.seventhnight.com/treestructs.asp

The third part has some info on how to manage a tree without rebuilding it.

Corey
Go to Top of Page
Page: of 7 Previous Topic Topic Next Topic  
Previous Page | Next Page
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000