This is for the construction of some views we'll be using for reporting.

I have an table of locations, the key fields being "location" and "parent".

The structure that these two fields create, level-wise, are along the lines of Company Name --> Campus Name --> Building Name --> Floor Name --> Room Name. Company name remains the same and Campus name remains the same in this case.

The structure of locations generally looks like this:

                                 | Org. Name |
           +--------------------+|Campus Name|+---+--+-------------+
           |                     +--+--------+    |                |
           |                        |             |                |
           |                        |             |                |
        +--+-----+           +------+-+        +--+----+       +---+---+
    +--+| BLDG-01|+--+       | BLDG-02|        |BLDG-03|       |Grounds|
    |   +--------+   |       +--------+        +-------+       +-------+
  +-+------+   +-----+--+
  |Floor-01|   |Basement+-------+
  +-+------+   +--------+       |
    |                           |
    |                           |
    | +----------+      +-------+--+
    +-+Room 1-001|      |Room B-002|
      +----------+      +----------+

Every location links back to its parent location, which is ultimately the organization name. Currently, there is only one organization and one campus.


  • I would like to be able to query all locations beneath any given location at the "Building" level. This is so I can return things like how many workorders have been performed for any location within a given building.
  • I would like to be able to determine which sub-location belongs to which building. Essentially the reverse; I would like to go from any level beneath the building level and trace back up to what the building is.
  • I would like this to be in a view. That means, I would like to have a table that for every item at the "building" level, lists the building in the left-hand column and all possible locations UNDER that building in the right-hand column. This way I'd have a list that I could query at any time to find which locations are part of which building.

Attempts and Doing it Right

I've attempted to do this through horribly constructed views, UNION queries, etc. -- which all have seemed like a bad idea. I know Oracle possesses a mechanism for this through "CONNECT BY"; I'm just not sure how to make use of it.

  • How are "root" nodes identified? Is parent NULL for them? How do you identify a "building level"?
    – user1822
    Commented Jul 3, 2012 at 13:09
  • @a_horse_with_no_name logically, I suppose the "building" level would be anything with a parent that is the campus name, i.e. anything with a parent of "MAINCAMPUS". The root of all the nodes is "COMPANYNAME", which is the parent of "MAINCAMPUS", and all buildings (plus "grounds") have MAINCAMPUS as a parent. Commented Jul 3, 2012 at 15:57
  • wow! how did you create that!! Google for "Adjacency Model in SQL" you will be all set Commented Jul 3, 2012 at 19:58
  • PS, for those who were interested in how I made the diagram, I used a nifty little web site called asciiflow.com -- I'm a big fan for situations like that. Commented Jan 14, 2015 at 18:59

3 Answers 3


FrusteratedWithFormsDesigner has the right direction(+1). Here is what I think you are looking for specifically.

   SELECT connect_by_root location "Building", location "SubLocation"
   FROM some_table l
   START WITH l.Location IN 
         SELECT location FROM
         SELECT level MyLevel, location FROM some_table 
         START WITH parent IS NULL 
         CONNECT BY PRIOR location=parent
         WHERE MyLevel=3   
   CONNECT BY PRIOR l.location = l.parent;

select * from BuildingSubs; 

Building             SubLocation        
-------------------- --------------------
BLDG-01              BLDG-01              
BLDG-01              Basement             
BLDG-01              Room B-002           
BLDG-01              Floor-01             
BLDG-01              Room 1-001           
BLDG-02              BLDG-02              
BLDG-03              BLDG-03              
Grounds              Grounds              

The view accomplishes all three goals. You can query it for a building to find everything it contains and you can query it for a sub-location to find what building it is in.

drop table some_table;
create table some_table (Location Varchar2(20), Parent Varchar2(20));

insert into some_table values ('Org. Name',NULL);
insert into some_table values ('MAINCAMPUS','Org. Name');
insert into some_table values ('BLDG-01','MAINCAMPUS');
insert into some_table values ('BLDG-02','MAINCAMPUS');
insert into some_table values ('BLDG-03','MAINCAMPUS');
insert into some_table values ('Grounds','MAINCAMPUS');
insert into some_table values ('Floor-01','BLDG-01');
insert into some_table values ('Basement','BLDG-01');
insert into some_table values ('Room B-002','Basement');
insert into some_table values ('Room 1-001','Floor-01');

If you don't want to count the building itself as one of the sub-locations you can wrap the existing query in one the eliminates entries in which the building and sublocation are the same.

  • Leigh, this was exactly it. Thank you for the help! Commented Jul 3, 2012 at 19:39

CONNECT BY is the correct way to handle data that is naturally recursive.

I don't know what your table looks like but maybe something like:

FROM some_table st
START WITH st.location = 'BLDG-01'
CONNECT BY PRIOR st.location = st.parent;

This should get nodes under "BLDG-01".

The START WITH clause is your base case.

Another explanation (aside from the Oracle one which I assume you've already read and had trouble with, it's probably very terse):






  • Thanks for the response! I'm getting it enough to realize that I don't think I phrased my question well. My table structure has two columns -- "location" and "parent". The hierarchy these create is defined by my ascii chart. I would like to construct a view that shows, for each location at the "building" level, all locations underneath its branch. My goal is to be able to query a building and get all its sub-locations, or query a sublocation and see which building it belongs to, through a view (so no hard-defined "building-x" in the query). Any help would be greatly appreciated! Commented Jul 3, 2012 at 12:43

I'm not sure I understand your question completely, but maybe something like this:

select location, 
       sys_connect_by_path(location, '/') as item_list,
       case level
         when 1 then 'building'
         when 2 then 'floor'
         when 3 then 'room'
       end as item_type
from some_table 
start with parent = 'MAINCAMPUS'
connect by prior location = parent;

This will show you the hierarchy for each location

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.