Creating self referencing table from multiple tables

[Paul Cook]

Hi,

I have three tables:

Countries:
ID
Country

States:
ID
State
CountriesID

Cities:
ID
City
StatesID

I want to roll these into a single self-referencing table using Access
or Sql Server:

Region:
ID
Name
ParentID

....where the top level (countries) would have a ParentID of null and
others would cascade from that.

Can anyone point me to a query to build this table?

Thanks, Paul.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'd probably save yourself a lot of maintenance by just having a table
like this:

CREATE TABLE Regions (
region_id COUNTER NOT NULL UNIQUE,
country VARCHAR(25) NOT NULL ,
[state] VARCHAR(25) NOT NULL ,
city VARCHAR(30) NOT NULL ,
CONSTRAINT PK_Region PRIMARY KEY (country, [state], city)
)

A COUNTER is an AutoNumber data type in Access/JET.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmUP3oechKqOuFEgEQIi0QCaAszdippbE3KG7yKwWkytFD lrND8An3A6
uxMRGLtlPEq2jZV0Gsqjlxc+
=HMoY
-----END PGP SIGNATURE-----

Paul Cook wrote:

Hi,

I have three tables:

Countries:
ID
Country

States:
ID
State
CountriesID

Cities:
ID
City
StatesID

I want to roll these into a single self-referencing table using Access
or Sql Server:

Region:
ID
Name
ParentID

...where the top level (countries) would have a ParentID of null and
others would cascade from that.

Can anyone point me to a query to build this table?

Thanks, Paul.

[Jeff Smith]

How are you going to link a city to a country if that country doesn't have
any states. The only countries that have official states that I know of is
the USA and Australia. There might be a few more but they are in the
minority.

Jeff
"Paul Cook" <pa******@hotmail.com> wrote in message
news:de**************************@posting.google.c om...

Hi,

I have three tables:

Countries:
ID
Country

States:
ID
State
CountriesID

Cities:
ID
City
StatesID

I want to roll these into a single self-referencing table using Access
or Sql Server:

Region:
ID
Name
ParentID

...where the top level (countries) would have a ParentID of null and
others would cascade from that.

Can anyone point me to a query to build this table?

Thanks, Paul.