Re: Join on hierarchy query using the root element possible?

From: Roel Schreurs <schreurs_roel_at_hotmail.com>
Date: 3 Oct 2004 02:23:29 -0700
Message-ID: <321ebdef.0410030123.48c2f4ca@posting.google.com>

Sybrand Bakker <sybrandb_at_hccnet.nl> wrote in message news:<6b5rl0ls3uj4p68nc7ucparls9bf8r120o_at_4ax.com>...
> On 1 Oct 2004 03:37:38 -0700, schreurs_roel_at_hotmail.com (Roel
> Schreurs) wrote:
>
> >Does something like that exist? Or does anybody know another way to
> >achieve this?
>
>
> Does your database have a version? The answer may be version
> dependent.
>
> Hint: connect by queries are allowed in subqueries, just transform
> your join to a subquery.

The oldest Oracle database version I need to comply with is 8.1.7, but if a higher version supports an alternative, I would be pleased to know it, for the future.

I understand that connect by queries can be used in sub queries, but I cannot see how I could join them correctly. The hierarchy must be joined at 'root' elements to the permissions table. However, as I explained in my question, I do not know how to get hold on the root element.

The table HIERARCHIE stores the following: 'ROOT'

	'A'	
		'AA'
	'B'

Since U1 is granted for 'ROOT', I should be able to calculate the following implicit permissions:

U1	'ROOT'
U1	'A'
U1	'AA'
U1	'B'
U2 is granted for 'A', which would imply:
U2	'A'
U2	'AA'

To achieve this, I need all sub trees as follows:
ROOT	NODE	LEVEL
'ROOT'	'ROOT'	1
'ROOT'	'A'	2
'ROOT'	'AA'	3
'ROOT'	'B'	2
'A'	'A'	1
'A'	'AA'	2
'AA'	'AA'	1
'B'	'B'	1

I need the column ROOT to join to the table PERMISSIONS, since I want to join complete sub-trees, not just rows.

I hope this clarifies my question.

Roel Schreurs Received on Sun Oct 03 2004 - 04:23:29 CDT