Trees! - 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 2000 Forums

SQL Server Development (2000)

Trees!

Author

Topic

mmahmed
Starting Member

6 Posts

Posted - 2006-05-18 : 13:15:08

Hi all

I'm backing my head against a wall with this problem and I hope someone will be able to help!

I have a table that stores hierarchical data. One of the fields is Hierarchy and holds information such as .1. .1.2. .1.2.10.

Now, say I run a query and it returns the following:
.2.
.2.10.
.2.10.83.
.2.10.84.
.3.
.3.29.
.3.77.
.3.77.90.

How can I return the follwing rows (these may not be leaf nodes):
.2.10.83.
.2.10.84.
.3.29.
.3.77.90.

The reason I ask is because the rows not returned are the parents of these (and the information already exists in the children's hierarcies).

Any help would be appreciated.

Cheers
Moosh

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-18 : 14:20:10

Do you have any other columns that define the hierarchy ie: [ID], [ParentID] ?

Be One with the Optimizer
TG

mmahmed
Starting Member

6 Posts

Posted - 2006-05-18 : 16:44:06

Hi TG

Yes. There is a ParentID field and a PostLevel field too.

Cheers
Moosh

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-18 : 17:03:57

Then perhaps something like this:


--get all rows that aren't a parent
select	hierarchy
from	<YourTable> a
left join (
	select distinct ParentID
	from <YourTable>
	) b
		on b.parentid = a.[ID]
where	b.parentid is null

Be One with the Optimizer
TG

mmahmed
Starting Member

6 Posts

Posted - 2006-05-18 : 17:28:38

Hi TG

Thanks for that. But that just gets back the leaf nodes.

My original question (which I should have explained further - my brain was hurting too much) displays the output:
.2.
.2.10.
.2.10.83.
.2.10.84.
.3.
.3.29.
.3.77.
.3.77.90.

My tree table is populated with more nodes than is displayed. The above is the result of the following query:

SELECT A.Hierarchy
FROM tblTree A INNER JOIN lnkTreeItem B ON A.TreeID = B.TreeID
WHERE B.ItemID = 1;

As you can see it is a one to many relationship. But the way the application is programmed the item is tagged from the node right back to its root with the parents along the way (hence the output above). However, I don't want to display the above because .2. is not unique in the list as it appears in .2.10. which is not unique because it appears in .2.10.83.

.2.10.83 does not appear in any of the other rows so I want to display this along with .2.10.84., .3.29. and .3.77.90. I can always workout who the parents are by counting backwards e.g. 90 is a child of 77 which is a child of 3.

As I mentioned earlier, the output set of nodes that I want returned may be parents or leaves.

Hopefully, that has explained it slightly better.

Cheers
Moosh

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-18 : 17:44:24

Sorry, I'm not following. Please post working DDL and DML like I have below along with the desired output. This is how I read your original post. My raw data matches your raw data and my output matches your desired output.

Please make whatever changes you want to the DML (raw data) and what the desired output is.


set nocount on
if object_id('tempdb..#h') > 0 drop table #h
create table #h (id int, parentid int, hierarchy varchar(10))
insert #h
select 1, null, '.2.' union
select 2, 1, '.2.10.' union
select 3, 2, '.2.10.83.' union
select 4, 2, '.2.10.84.' union
select 5, null, '.3.' union
select 6, 5, '.3.29.' union
select 7, 5, '.3.77.' union
select 8, 7, '.3.77.90.'

select * from #h

--get all rows that aren't a parent
select	hierarchy
from	#h a
left join (
	select distinct ParentID
	from #h
	) b
		on b.parentid = a.[ID]
where	b.parentid is null

output:
id          parentid    hierarchy  
----------- ----------- ---------- 
1           NULL        .2.
2           1           .2.10.
3           2           .2.10.83.
4           2           .2.10.84.
5           NULL        .3.
6           5           .3.29.
7           5           .3.77.
8           7           .3.77.90.

output: (matches what you have above?)
hierarchy  
---------- 
.2.10.83.
.2.10.84.
.3.29.
.3.77.90.

EDIT: (DDL/DML)
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

mmahmed
Starting Member

6 Posts

Posted - 2006-05-19 : 04:51:27

Hi TG

First of all thanks for persevering.

Secondly, some sample code:


SET NOCOUNT ON;

DECLARE @tblTree TABLE 
(
	TreeID int, 
	ParentTreeID int, 
	Hierarchy varchar(900)
);

DECLARE @lnkTreeItem TABLE 
(
	TreeID int, 
	ItemID int	-- item is a pk from another table
);

INSERT INTO @tblTree(TreeID, ParentTreeID, Hierarchy)
SELECT 1, null, '.2.' UNION
SELECT 2, 1, '.2.10.' UNION
SELECT 3, 2, '.2.10.83.' UNION
SELECT 4, 3, '.2.10.83.101.' UNION
SELECT 5, 3, '.2.10.83.102.' UNION
SELECT 6, 3, '.2.10.83.103.' UNION
SELECT 7, 3, '.2.10.83.104.' UNION
SELECT 8, 2, '.2.10.84.' UNION
SELECT 9, null, '.3.' UNION
SELECT 10, 9, '.3.29.' UNION
SELECT 11, 10, '.3.29.50' UNION
SELECT 12, 9, '.3.77.' UNION
SELECT 13, 12, '.3.77.90.'

INSERT INTO @lnkTreeItem(TreeID, ItemID)
SELECT 1, 1 UNION
SELECT 2, 1 UNION
SELECT 3, 1 UNION
SELECT 8, 1 UNION
SELECT 9, 1 UNION
SELECT 10, 1 UNION
SELECT 12, 1 UNION
SELECT 13, 1

SELECT 
	A.Hierarchy
FROM 
	@tblTree A INNER JOIN 
	@lnkTreeItem B ON A.TreeID = B.TreeID
WHERE 
	B.ItemID = 1;

SET NOCOUNT OFF;

Output


.2.
.2.10.
.2.10.83.
.2.10.84.
.3.
.3.29.
.3.77.
.3.77.90.

But my desired output is:


.2.10.83.
.2.10.84.
.3.29.
.3.77.90.

Notice that .2.10.83. and .3.29. are parents but in the item/tree link table only the parent nodes have been chosen.

I could use the desired output to display a breadcrumb if I so wished. The reason I don't require .2., .2.10., .3. and .3.77. is because they are already encoded in the desired output rows.

***** EURIKA *****
I have finally figured out what I want to do!

The answer to my question is:



SELECT 
	Hierarchy
FROM 	
	(
		SELECT 
			A.TreeID, A.Hierarchy
		FROM 
			@tblTree A INNER JOIN 
			@lnkTreeItem B ON A.TreeID = B.TreeID
		WHERE 
			B.ItemID = 1
	) DRVDesiredA LEFT OUTER JOIN 
	(
		SELECT 
			A.ParentTreeID
		FROM 
			@tblTree A INNER JOIN 
			@lnkTreeItem B ON A.TreeID = B.TreeID
		WHERE 
			B.ItemID = 1
	) DRVDesiredB ON DRVDesiredB.ParentTreeID = DRVDesiredA.TreeID
WHERE
	DRVDesiredB.ParentTreeID IS NULL;

Output


.2.10.83.
.2.10.84.
.3.29.
.3.77.90.

Because I am just after the leaf-nodes (which your original query does) of my subset.

Again, thank you for your help!

I think sometimes we just need to chat for the answer to present itself!

Cheers
Moosh

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-05-19 : 12:48:05

you could also look around here (http://www.thesitedoctor.co.uk/seventhnight/treestructs/) for some more detail on trees..

Use to be on my site, but its been down...

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-20 : 01:53:02

>>Use to be on my site, but its been down...

What happended to your Server?
Your random sig also not working

Madhivanan

Failing to plan is Planning to fail

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-05-22 : 09:08:32

No high-speed internet options where I moved to... (yet!)

Refuse to pay for a host.

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources