Iterative select. Recursive Grandparent, parent, Child.

[hwkranger]

I have dream... I mean, I have a question.

I sort of have this solved to a point, but not exactly.

I have a table of dependancies. It looks like:

[ID BIGINT] [Dependant_ID BIGINT]

ID is the object, and Dependant ID is all the objects that the ID relies on. This can go in circles. example: 1 can depend on 2, 2 can depend on 3, 3 can depend on 1. (Or even 2 can depend on 1) It's a bit of a mess, that I'm cleaning up.

Anyway. I'd like to see a mapping but, when an item relies on a parent, I would to show that it relies on the parent, but not all the sub's of the parent.

example query:

SELECT
 D1.Table_ID, D2.Table_ID, D3.Table_ID, D4.Table_ID
FROM 
 database_depencancy AS D1
   LEFT JOIN database_depencancy AS D2
    ON D1.ID = D2.Table_ID
   LEFT JOIN database_depencancy AS D3
    ON D2.ID = D3.Table_ID
   LEFT JOIN database_depencancy AS D4
    ON D3.ID = D4.Table_ID
GROUP BY D1.Table_ID, D2.Table_ID, D3.Table_ID, D4.Table_ID

Example returned data from query:

D1.Table_ID D2.Table_ID D3.Table_ID D4.Table_ID
444
444 459
444 459 444
444 459 444 459
444 459 444 461
444 459 444 471
444 459 444 473
444 459 444 474
444 459 444 476
444 459 444 480
444 459 444 507
444 459 444 550
444 459 444 959

Notice how 444 is listed under D3.Table_ID. (Skip the fact that they are called table's. This is actually a mapping of database dependancies from access scripted into a database for querying)

Cheers!

Randall Vollen
Merrill Lynch