By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,231 Members | 1,636 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,231 IT Pros & Developers. It's quick & easy.

Creating self referencing table from multiple tables

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
-----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.

Nov 13 '05 #2

P: n/a
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.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.