I am having problems with a hierarchial query and I am hoping someone can point me in the right direction. I am running Oracle 9i and I am trying to create a hierarchial query with a join to get a related id in the result set. I can do the hierarchial part no problem, however when I try to introduce the id from the related table everything falls apart. I have 2 tables with the following columns (I have left out unneeded columns for clarity): PROVIDER provider_id provider_contract_status_id PROVIDER_CONTRACT_STATUS provider_contract_status_id prev_prov_contract_status_id The PROVIDER table has the most current contract status id and there is a FK relationshiop between PROVIER & PROVIDER_CONTRACT_STATUS based on the provider_contract_status_id. I can easily show the hierarchy in the PROVIDER_CONTRACT_STATUS table as follows: select provider_contract_status_id status_id, prev_prov_contract_status_id prev_id, level from provider_contract_status start with provider_contract_status_id in (select provider_contract_status_id from provider) connect by prior prev_prov_contract_status_id = provider_contract_status_id; status_id prev_id level 87944200 87825800 1 87825800 2 87332200 1 88249600 88173600 1 88173600 88169600 2 88169600 3 I have been asked to create a view showing the above hierarchy as well as the corresponding provider_id from the PROVIDER table, like the following: provider_id curr_id prev_id level 323232 87944200 87825800 1 323232 87825800 2 000001 87332200 1 006400 88249600 88173600 1 006400 88173600 88169600 2 006400 88169600 3 I have tried different variations of adding the PROVIDER table to the from and adding a where clause to no avail. Can someone point me in the right direction as to how I might be able to accomplish this? Thanks - Mike -- //www.freelists.org/webpage/oracle-l