SQL Help: display country/state/city structure
952498Jul 27 2012 — edited Jul 30 2012Hi all,
I need to write a query to generate county/state/city structure.
Exiting table Example:
COUNTRY STATE CITY NAME
1 0 0 US
1 1 0 NY
1 1 1 NYC
1 1 2 Long Island
1 2 0 CA
1 2 1 San Francisco
2 0 0 Canada
2 1 0 Ontario
2 1 1 Toronto
2 2 0 Qubec
2 2 1 Montreal
New Table structure:
country county name state state name city city name
1 US 0 0
1 US 1 NY 0
1 US 1 NY 1 NYC
1 US 1 NY 2 Long Island
1 US 2 CA 0
1 US 2 CA 1 San Francisco
2 Canada 0 0
2 Canada 1 Ontario 0
2 Canada 1 Ontario 1 Toronto
2 Canada 2 Qubec 0
2 Canada 2 Qubec 1 Montreal
Since this reference table has a lot of records, like 50,000, what's the most efficient way to write it? I tried self join, it's taking a long time. I am using Oracle 11g.
Thanks,
Jane