Retrieve tree structure from table

[Schweiger]

I've got a Table with the following tree structure:

ID    Name            Parent
---   --------------  ---
  1   EUROPE            0
 12   CENTRAL EUROPE    1
 30   SWITZERLAND      12
 40   FRANCE           12
 50   ITALY            12
 13   SCANDINAVIA       1
 60   SWEDEN           13
 70   DENMARK          13
 ...

How can I extract all data from a specific node of the tree with select or other statement, or are there any links I could go to look up how one handles retrieving such tree structures from a datatable?
Any suggestions would be highly appreceated
thanks in advance
Andreas

 

[Mike Gagnon]

Schweiger

ID Name Parent
--- -------------- ---
1 EUROPE 0
12 CENTRAL EUROPE 1
30 SWITZERLAND 12


The only I can think of is, I'm not sur want you need:

SELECT *, FROM myTable;
WHERE myTable.ID = 30;
INTO CURSOR myCursor
BROWSE

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first

 

[Schweiger]

mgagnon
I would like to choose an Id, e.g. 13 and my query should return all the nodes below this ID and the record with this ID itself:

---   --------------  ---
 13   SCANDINAVIA       1
 60   SWEDEN           13
 70   DENMARK          13

If I would pick 1, then I would get every record in my example. In the treeview e.g. the nodes are in such a hierarchical order too, but there every node knows his childs whereas in my table you know only the parent. This way the table would be handy and intuitive to me, but I know I could have a second table to manage the relationship between the single recordsets. But I would prefere a single table if there would be a possibility to get complete substructures out of the whole table.

Hope I could explain me better... Perhaps I''ve to study some english before going on
Andreas

 

[danceman]

What you need is SELF JOINS. look at faq184-2168 for guidance. Attitude is Everything

 

[Schweiger]

thank you very much danceman
I've had a look at it and the following is my try. But the backdraw is, that I've got to know the depth of the tree to set up the correct select statement. But it works for my actual project like that, if this select works correct:

SELECT DISTINCT

 one.ID,one.Name,one.parent ;

FROM

 test

AS

 one ;

JOIN

 test

AS

 two

ON[code][/color][code] (one.parent=two.id)

.OR.

 (one.id=p);

JOIN

 test

AS

 thr

ON

 two.parent = thr.id ;

JOIN

 test

AS

 fou

ON

 thr.parent = fou.id ;

JOIN

 test

AS

 fiv

ON

 fou.parent = fiv.id ;

WHERE

 (one.parent = p)

.OR.

 (two.parent = p)

.OR.

 ;
      (thr.parent = p)

.OR.

 (fou.parent = p)

.OR.

 ;
      (fiv.parent = p )

.OR.

 (one.id = p)

I can store an ID to the variable p and the select returns all nodes incl. the one in p of the tree, exactly as I need them.
There isn't a recursive possibility to retrieve the same information without having to know the depth of the tree is there?

Andreas

 

[danceman]

there is a recursive syntx that will drill up to the top. I havn't done this in a long time. I will have to play with that.

I did this sometime for a company that wanted to do just that, wish I took the code with me. this method is used by the coast guad in a database they have for all aircraft parts. they use it to find the child or parents of any part on an aircraft.

give me your email address, I will work in it. Attitude is Everything

 

[mm0000]

I dont think it is possible with VFP. I know you can do with the SQL implementation in Oracle using 'Connect by prior' - in Oracle you would do it with a SQL statment like:

SELECT id,name,parent FROM mytreetable START WITH id =[root-note-id] CONNECT BY PRIOR id = parent

Unfortunately this is not standard SQL and therefore such a statement would not be possible in VFP

 

[Schweiger]

danceman

My eMail is: j.schweiger@bluemail.ch

I'm working with VFP6.0. But if this is a problem I think this would be a good argument to upgrade.

I would be very glad to get your suggestions, many thanks in advance.
Andreas