Hierarchy Trees - Can They Be Done?

[jgorny]

Hi,

 

I am trying to work out a way to get a flat view, but having a difficult time trying to figure out how to make Caspio return what I need.

 

I have a table of locations, and a table of workers.

 

Each worker works at one location.

 

Each location has one parent. A location can have many children.

 

Say the location table has:

Loc_ID | Location | Parent_ID
-----------------------------
1      | USA      | NULL
2      | Oregon   | 1
3      | Portland | 2

Now say I have a worker who had credentials at the national headquarters.

 

I can create a flat view that connects him directly to his location - say a view that only allows someone who works at the national office to log in.

 

I can also create a view that goes three nodes deep as ID/Location Pairs:

 

Loc_ID/Location/Par_ID/Par_location/Gpar_ID/Gpar_location

 

How can I create a view that looks like this?

Wrk_ID | Worker | Loc_ID | Location   | Par_ID | Parent Loc | Gpr_ID | GParent Loc
----------------------------------------------------------------------------------
1      | Jerry  | 1      | USA        | --     | --         | --     | --
1      | Jerry  | 2      | Oregon     | 1      | USA        | --     | --
1      | Jerry  | 3      | Portland   | 2      | Oregon     | 1      | USA

This type of view would be easy to create in SQL, but I am having a hard time getting the result I want in Caspio.

[aam82]

I think you would have to limit your hierarchy to child, parent, and grandparent tables, and join the tables accordingly.

 

If you can't pre-define the number of levels in your hierarchy, then you would have to somehow conditionally loop for joins, in order to extend for an unlimited amount of columns? I don't know if SQL has such a thing?

[DataCobalt]

What is the top level of this heirarchy? In your posting you made it sound like it was Location, but in the table you have the worker related to many locations?