Problem adding a join to a hierarchial query

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
--
http://www.freelists.org/webpage/oracle-l

Other related posts: