Recursive query, or similar

[James Minns]

I would like to know if it is possible to "explode" a table via a query;
here is a small example

Table fields
ID number
Type text (E or M)
Source_Ref text
Dest_Ref text

The "type" field can be either E for Element or M for Mix.

the following table should be in columns...
ID Type Source_Ref Dest_Ref
0 E E1 D1
1 E E2 D2
2 E E3 D3
3 M E1 D4
4 M E2 D4
5 M D4 D5
6 M E3 D5

I need a method to obtain the components of Dest_Ref D5,
which is a mix of D4 and E3. D4 is a mix of E1 and E2 so the
component list should be:

Element
E3
E1
E2

The Source_Ref field should be "recursively exploded" until all the mixes
are resolved to elements. There is no way of knowing how many 'nesting'
levels there might be in the data.

Is there any way to get this result in an SQL query?


Thanks for any help,
James

 

[Allen Browne]

Recursive data is something SQL does not handle easily.

Joe Celko has some articles dealing with the theory:
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

In practice, there's usually the practical issue of infinite recursion. It
probably represents bad data, but you cannot ignore the prroblem. So
whenever I have had to work with this kind of data, I have always cheated
and insisted that the data must completely resolve in a finite number of
steps (usually between 4 and 8, depending on the data.) I then use VBA to
loop through and resolve all the records into a temp table. If it doesn't
resolve, we bail out and ask the user to fix it. If it does resolve, the
temp table makes life dead easy (and very efficient) to query anything
further you want to do.

 

[Michel Walsh]

Hi,

If you use MS SQL Server 2005 (not 2000 or less), and if your table is
called Mixtures, with last two fields renames source and dest, then the
following works:

----------------------------
WITH this (mySource)
AS
(
SELECT m.source FROM mixtures AS m WHERE m.dest = 'D5'

UNION ALL

SELECT q.source FROM mixtures AS q
INNER JOIN this ON q.dest =this.mySource
)

SELECT *
FROM this
WHERE mySource LIKE 'E%'
--------------------------

All-caps-words are key words, and must be typed as they are, other words can
be modified to suit your design.

If your data is on MS SQL Server 2005 and you use Jet, then you can try to
use a pass-through query.

If your data is on JET (and that you use JET), but you have MS SQL Server
2005 (or Express), then, from MS SQL Server, create a database, then add
sp_addlinkedserver to your Jet database where the data resides (or use any
other "linking" to your liking, from MS SQL Server to your Jet table) and,
from JET, now use a pass-through query, but this time, taking into account
that the SQL statement is executed from MS SQL Server that has to see your
table as a (heterogeneous, outside) linked table.


If you don't have MS SQL Server 2005, well, better forget about that
solution.



Hoping it may help,
Vanderghast, Access MVP

 

[James Minns]

Recursive data is something SQL does not handle easily.

Joe Celko has some articles dealing with the theory:
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

In practice, there's usually the practical issue of infinite recursion. It
probably represents bad data, but you cannot ignore the prroblem.

Thanks for the links!
I tried the recursion route in VBA, and I have indeed got a problem with
infinite recursion: a certain step of the procedure involves transferring
from the bottom of one mix vessel to the top of the same vessel - its
necessary to document this process (source and destination are the same).
I'm going to re-evaluate the tables and data.

James

 

[Michel Walsh]

Hi,



With the following nested tree:


mixtures
id lft rgt name
D3 12 15 D3
D4 3 8 D4
D5 2 11 D5
E1-1 6 7 E1
E2-1 4 5 E2
E3-1 9 10 E3
E3-2 13 14 E3
top 1 16 top




the (overly simple, simplistic) query

-----------------------------------------
SELECT name
FROM mixtures
WHERE lft Between 2 And 11
AND rgt=1+lft;
-------------------------------------------


returns the desired result. The "magic" numbers 2 and 11 are the lft and rgt values of the node, here D5, that you want.


The graphical representation of the nested tree:


TOP (1-16)
- D5 (2-11)
- D4 (3-8)
- E2a (4-5)
- E1a (6-7)
- E3a (9-10)
- D3 (12-15)
- E3b (13-14)


The labeling (lft-rgt) is obtained like this:
Start with TOP (1- ), get one child, D5 (2- ), get one child D4 (3- ), get one child, E2 (4- ) and no child, so close the lineage E2(4-5). D4 (3-) has another child, E1 (6-), which ends the lieage, E1(6-7), D4(3-) has no more children, so its lineage is close with D4(3-8). D5 (2- ) has a another child, E3 (9-) with no heir, so E3 (9-10), closing D5, D5(2-11), and TOP has another child, D3(12- ), itself with a child E3(13-), end of lineage, E3(13-14) also closing D3, D3(13-15), closing TOP (1-16).


Since a tree cannot have cycles, each E3 node has to be different, in the tree, but nothing forbid us to fill it with the same "thing". See how we simply did it in the proposed mixtures table: we add a column, name, and pump that column from the query. Simple and performant.



Hoping it may help,
Vanderghast, Access MVP

 

[James Minns]

Hi,

Michel, thank you for your answers, I have a lot to evaluate. The SQL
solution is what I was hoping for, though my data is on an MSDE database,
with an access front end - so no luck there.

Thanks again.

James