One way is to write an SP and in it you create a temp table (#temp in this
example) with one field called ID then......
-- Insert initial records
INSERT INTO #Temp (ID)
SELECT ID FROM table WHERE Boss = @ID
--Insert all terms further down the heirarchy
DECLARE @rowcount int
, @rowcount1 int
SELECT @rowcount = 1
SELECT @rowcount1 = 0
WHILE @rowcount <> @rowcount1
BEGIN
SELECT @rowcount1 = @rowcount
INSERT INTO #temp (ID)
SELECT DISTINCT ID
FROM table, #temp
WHERE table.Boss = #temp.ID
SELECT @rowcount = @@rowcount
END
Then select distinct ID from temp and you have your list.........
------------------------------------------------------------------
Andrew Ewings
Project Manager
Thoughtbubble Ltd
http://www.thoughtbubble.net
------------------------------------------------------------------
United Kingdom
http://www.thoughtbubble.co.uk/
Tel: +44 (0) 20 7387 8890
------------------------------------------------------------------
New Zealand
http://www.thoughtbubble.co.nz/
Tel: +64 (0) 9 488 9131
------------------------------------------------------------------
The information in this email and in any attachments is confidential and
intended solely for the attention and use of the named addressee(s). Any
views or opinions presented are solely those of the author and do not
necessarily represent those of Thoughtbubble. This information may be
subject to legal, professional or other privilege and further distribution
of it is strictly prohibited without our authority. If you are not the
intended recipient, you are not authorised to disclose, copy, distribute, or
retain this message. Please notify us on +44 (0)207 387 8890.
-----Original Message-----
From: Jason Blum [mailto:[EMAIL PROTECTED]]
Sent: 29 October 2001 17:07
To: CF-Talk
Subject: *** Complicated SQL riddle
Howdy all!
Here's a SQL problem that's been vexing me for a few weeks now:
Say you have a table like this:
ID Name Boss
1 John 1
2 Mary 2
3 Steve 1
4 Mike 2
5 Susan 3
6 Max 2
7 Michelle 6
So, John is his own boss, the big cheese, and everybody reports to him
or to someone who reports to him, or to someone who reports to someone
who reports to him, etc.
That's the problem - I need to keep it flexible to accommodate any
number of levels because I want to output a tree illustrating the
hierarchy:
-John
---Mary
------Mike
------Max
---------Michelle
---Steve
------Susan
I think I've heard someone talking about "self-joins" but am not sure
how they'd work and whether they'd be flexible enough - anyone have any
ideas?
-Jason
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
