So, is it true that each "root" in PROVIDER_CONTRACT_STATUS table has only one leaf? Igor Neyman, OCP DBA ineyman@xxxxxxxxxxxxxx -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mike Spragg Sent: Wednesday, November 17, 2004 12:26 PM To: oracle-l@xxxxxxxxxxxxx Subject: Re: Problem adding a join to a hierarchial query Thanks everybody for the help! By tweaking your suggestions I have come up with the following: select p.provider_id, pc.provider_contract_status_id, pc.prev_prov_contract_status_id from (select provider_contract_status_id curr_id, prev_prov_contract_status_id prev_id 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) pc, provider p where p.provider_contract_status_id (+) = pc.provider_contract_status_id; provider_id curr_id prev_id 323232 87944200 87825800 87825800 000001 87332200 006400 88249600 88173600 88173600 88169600 88169600 Is there any way to get the following result where the provider_id is populated on every row? provider_id curr_id prev_id 323232 87944200 87825800 323232 87825800 000001 87332200 006400 88249600 88173600 006400 88173600 88169600 006400 88169600 Thanks again - Mike -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l