Jump to content
  • 0

Hierarchy Trees - Can They Be Done?





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:




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.

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

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?

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...