Recursive Query

[Jörg Aulich]

Hi,

is it possible to make a recursive query in Access 2000?

Background: I have a table with the following layout:

ID : LongInteger
DESCRIPTION : Text
ParentID : LongInteger

The column ParentID references to a datarow of the same table, so I am able
to construct a tree of rows. Is it possible to make a query with a parameter
([PARAM]), that retrieves the row with ID=[PARAM] and the rows with the IDs
= ParentID of this row and so on?

Thanks

Jörg

[Bob Barrows]

Its technical name is "correlated subquery".
Select *
FROM table
Where ID = [param]
OR
ID IN (Select ID FROM table WHERE ParentID = [param])

HTH,
Bob Barrows
Please reply to the newsgroup. My reply-to address is my "spam trap" and I don't check it very often.

[Jörg Aulich]

Hi Bob,

thanks for your answer, but unfortunately its not what I want. With this
correlated subquery, I get only the direct "childs" of a row, but I also
want the child's of these childs and so on to the leafes of the tree.

Any othe suggestions ?

Regards

Jörg
"Bob Barrows" <reb_...@yahoo.com> schrieb im Newsbeitrag
news:3baf2e85...@news.charter.net...

[Bob Barrows]

On Mon, 24 Sep 2001 15:31:36 +0200, "Jörg Aulich"
<aul...@campana-schott.de> wrote:

>Hi Bob,
>
>thanks for your answer, but unfortunately its not what I want. With this
>correlated subquery, I get only the direct "childs" of a row, but I also
>want the child's of these childs and so on to the leafes of the tree.
>
>Any othe suggestions ?
>

:-)
Better initial specs?

How many levels do you need to go? If it's unlimited, then you may
need to go with VBA. Otherwise, just keep adding to this query, as
deep as you need to go:

Select *
FROM table
Where ID = [param]
OR
ID IN (Select ID FROM table WHERE ParentID = [param])

OR ID IN (
SELECT t3.ID
FROM (table AS t1 INNER JOIN table AS t2 ON t1.ID = t2.ParentID) INNER
JOIN table AS t3 ON t2.ID = t3.ParentID
WHERE t1.ID =[param])
OR ID IN (
SELECT t4.ID
FROM ((table AS t1 INNER JOIN table AS t2 ON t1.ID = t2.ParentID)
INNER JOIN table AS t3 ON t2.ID = t3.ParentID)
INNER JOIN table AS t4 ON t3.ID = t4.ParentID
WHERE t1.ID =[param])
OR ID IN (
SELECT t5.ID
FROM (((table AS t1 INNER JOIN table AS t2 ON t1.ID = t2.ParentID)
INNER JOIN table AS t3 ON t2.ID = t3.ParentID)
INNER JOIN table AS t4 ON t3.ID = t4.ParentID)
INNER JOIN table AS t5 ON t4.ID = t5.ParentID
WHERE t1.ID =[param])

ad nauseam

If you want a VBA solution let me know - I wrote a similar one a few
months ago that you can have a try at modifying to fit your situation.

[Brian Camire]

Access does not support recursive queries of arbitrary depth. You can work
around this to some extent by modifying the structure of your tables and
storing additional information. You may find the following article helpful
in this regard:

http://www.vbpj.com/upload/free/features/vbpj/2001/07jul01/sqlpro0107/rein01
07/rein-1.asp

Another option might be to do what you need to do using VBA code instead of
a query. You could store the results in a temporary table where they could
be used in a query or report.

If it helps, Oracle, unlike Access, does support recursive queries through
the "START WITH" and "CONNECT BY" keywords.

"Jörg Aulich" <aul...@campana-schott.de> wrote in message
news:uRlboCPRBHA.1432@tkmsftngp04...

[Michel Walsh]

Hi,

Definitively, with nested sets. See
http://www.mvps.org/access/queries/qry0023.htm as an example of a recursive
query with unlimited depth, without a single line of VBA. And the technique
is around a thousand times faster than in Oracle (Joe Celko's claim). The
theory is explained at http://www.dbmsmag.com/9603d06.html

Hoping it may help,
Vanderghast, Access MVP

"Jörg Aulich" <aul...@campana-schott.de> wrote in message
news:uRlboCPRBHA.1432@tkmsftngp04...

[Bob Barrows]

On Mon, 24 Sep 2001 17:57:19 -0400, "Michel Walsh"
<Vande...@msn.com> wrote:

>Hi,
>
>
>
>Definitively, with nested sets. See
>http://www.mvps.org/access/queries/qry0023.htm as an example of a recursive
>query with unlimited depth, without a single line of VBA. And the technique
>is around a thousand times faster than in Oracle (Joe Celko's claim). The
>theory is explained at http://www.dbmsmag.com/9603d06.html
>
>

Sheesh!
I can't believe I forgot about that! Probably because I've never used
it. Here's a more useful article because it shows how to generate the
nested set from an adjacency list.

<http://www.intelligententerprise.com/001020/celko.shtml>

[Jörg Aulich]

Thanks Bob,

I need much more levels, so a VBA solutin will be necessary. It would be
very kind if you could mail me your solution, so I can take a look on it.

Regards

Jörg

"Bob Barrows" <reb_...@yahoo.com> schrieb im Newsbeitrag

news:3baf64ae...@news.charter.net...

[Jörg Aulich]

Thank your very much. That nailed it !!!!

"Michel Walsh" <Vande...@msn.com> schrieb im Newsbeitrag
news:eiWl9PURBHA.1476@tkmsftngp04...

[Bob Barrows]

On Tue, 25 Sep 2001 10:16:37 +0200, "Jörg Aulich"
<aul...@campana-schott.de> wrote:

>Thanks Bob,
>
>I need much more levels, so a VBA solutin will be necessary. It would be
>very kind if you could mail me your solution, so I can take a look on it.
>
>Regards

Are you sure? After seeing Michael Walsh's post?

[Jörg Aulich]

No, thanks Bob. After Michaels post I don't need it anymore.

"Bob Barrows" <reb_...@yahoo.com> schrieb im Newsbeitrag

news:3bb084d6...@news.charter.net...