Searching a tree from bottom to top

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

Transact-SQL (2000)

Searching a tree from bottom to top

Author

Topic

Eniac
Starting Member

4 Posts

Posted - 2002-08-09 : 13:33:58

I'm working on a Boss/Employee tree that will allow an Employee to have an infinite number of bosses.

I've read this article :
http://www.sqlteam.com/item.asp?ItemID=8866

It's quite helpful really and I managed to do pretty much I wanted to do even if I had to deal with multiple trees at once.

But...being the newbie that I am in SQL, I'm having a hard time reading all the bosses for a given employee.

Taking this test data, how could you get all the bosses of employee #12 ?

(I hope tabbing will be ok... :|)

EmployeeID ParentID Path Depth
1 0 / 1
2 1 /1/ 2
3 1 /1/ 2
4 2 /1/2/ 3
5 2 /1/2/ 3
6 2 /1/2/ 3
6 3 /1/3/ 3
6 16 /1/16/ 3
8 4 /1/2/4/ 4
9 4 /1/2/4/ 4
10 5 /1/2/5/ 4
11 5 /1/2/5/ 4
12 5 /1/2/5/ 4
12 6 /1/2/6/ 4
12 11 /1/2/5/11/ 5
12 6 /1/3/6/ 4
13 6 /1/16/6/ 4
13 6 /1/2/6/ 4
13 6 /1/3/6/ 4
16 1 /1/ 2

I've been thinking about something like this

Select * From tmpStorePath
Where ResourceID IN
(Select ResourceID from tmpStorePath Where CHARINDEX('/' + Str(ResourceID) + '/', (Select Path from tmpStorePath where ResourceID=12)) <> 0)

(this one doesn't work)

or something like that anyway....

Any ideas ??

Thanks

Eniac

-- Eniac --

-----------------------------------
Experience is what makes you pause
briefly before going ahead making
the same mistake
-----------------------------------

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-09 : 14:27:13

SELECT * FROM tmpStorePath
WHERE (SELECT Path FROM tmpStorePath WHERE EmployeeID=12)
Like '%/' + Cast(EmployeeID as varchar) + '/%'

It's a little funky but should get you all of the superior nodes (bosses).

Eniac
Starting Member

4 Posts

Posted - 2002-08-09 : 14:39:04

I already tried something more or less like this and I get this error

(Using my or your technique)

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

From what I understand, it means that I have from manipulate the subquery so that it is not the left-most expression.

Thanks!

[Oh and...please don't mind the email I sent you, I never expected you'd take the time to read my post and I figured I'd contact the author so...the email is only redundant now :-)]

-- Eniac --

-----------------------------------
Experience is what makes you pause
briefly before going ahead making
the same mistake
-----------------------------------

Edited by - eniac on 08/09/2002 14:42:25

Eniac
Starting Member

4 Posts

Posted - 2002-08-09 : 15:08:08

Got it, well, not me actually, a guy from ASPMessageboard helped me.

here's the answer if any are interested :

Select Distinct t.EmployeeID
From TempEmployee T, (select ParentID, Path from tempEmployee Where EmployeeID = 12) as ParentPaths
Where ParentPaths.Path like '%' + convert(varchar(5),t.EmployeeID) + '/%'
Order by t.EmployeeID
[Thanks goes to DRS1]
[url]http://www.aspmessageboard.com/forum/databases.asp?M=462131&F=21&P=1[/url]

Thanks again Rob for taking the time to help me.

-- Eniac --

-----------------------------------
Experience is what makes you pause
briefly before going ahead making
the same mistake
-----------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources