Link to home
Start Free TrialLog in
Avatar of sbornstein2
sbornstein2

asked on

PL/SQL Query Question Traverse help - need a guru

Hello all,

This is what I have.  I have a table that has the following example data:

FACILITY_NAME  FLOOR_NAME   ROOM_NAME   RACK_NAME
250 MA                  5th         5c-250                  F
250 MA                  5th         5c-250                  Unknown

I then have a table as so:
ID           LOCATION_NAME     PARENTID
101        250 MA                1
260        5th                101
264        5C-250                260
645        F                                  264

So I need to pull back for the 1st record the ID 645 as I find the ParentID all the way to the Rack Name.

In  the case of the second one for Unknown I would only go to 264.  So I need to get the first result set then append an ID column showing that last traversed ID.

No need to worry about the Parent ID of the 250 MA record it would start from the facility name.

Hope this makes sense and someone can assist in a sample.
Avatar of David VanZandt
David VanZandt
Flag of United States of America image

Sorry, the sample data is insufficient to follow your relational logic -- IMHO.
Avatar of sbornstein2
sbornstein2

ASKER

Why is it insufficient?
The first record set you would take the Facility Name and lookup the 250MA and get the ID of that:  101

Then you would take Floor Name 5th and look that up Where Location_Name = 5th and ParentID = 101:  that would get ID 260.

Then you would take the Room Name 5c-250 Where Location_Name = 5c-250 and ParentID = 260: that would get ID 264

Then you would take the Rack Name F Where Location_Name = F and ParentId = 264:  this would get the number I want 645 for this record.
ASKER CERTIFIED SOLUTION
Avatar of flow01
flow01
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks I ended up using a join to the same table parent id to id then did a merge statement that was the way to do this.