Please help with hierarchical query

Hi,

I have a table with sample records as below

Parent Region		Child Region


=============		============

North America		USA
USA			CALIFORNIA
USA			CONNECTICUT
CALIFORNIA		ORANGE COUNTY
CONNECTICUT		FAIRFIRLD COUNTY
FAIRFIELD COUNTY	NORWALK

I want to create a flattened table / materialized view such as below

Parent Region		Child Region


=============		============

North America		USA
North America		CALIFORNIA
North America		CONNECTICUT
North America		ORANGE COUNTY
North America		FAIRFIELD COUNTY
North America		NORWALK
USA			CALIFORNIA
USA			CONNECTICUT
USA			ORANGE COUNTY
USA			FAIRLFIELD COUNTY
USA			NORWALK
CALIFORNIA		ORANGE COUNTY
CONNECTICUT		FAIRFIELD COUNTY
CONNECTICUT		NORWALK
FAIRFIELD COUNTY	NORWALK

I am at loss as to how do I write a query to do this. I can do it by writing a stored procedure but was thinking whether its poccible using connect by in a single query to achieve this.

Thanks
Ron Received on Fri Jul 09 2004 - 14:34:40 CDT