Search the Community

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.