| Hugo Kornelis 2005-05-25, 7:23 am |
| On Wed, 25 May 2005 02:23:02 -0700, CJ wrote:
>hello!
>I have a hierarchy structure copied from excel to a table in databse SQL.
>Here is what i have in SQL table:
(snip)
Hi CJ,
Do you mean that each row has either Continent, or Country, or City
populated, but none of the other columns? I guess you're out of luck
then. Since there is no inherent order in a relational table, there is
no difference between the table you posted and this one:
Continent Country Ci
ty
Africa
Asia
Europe
Denmark
Germany
Japan
Kina
Norway
South-Africa
Sweden
Aalborg
Bergen
Gotaborg
Hannover
Hamburg
Hirroshima
Kopenhagen
Munchen
Oslo
Pretoria
Stockholm
Shanghai
Tokyo
Trondheim
Besides - since this table has no key, it is officially not a relational
table at all. Of course, terminology is pretty much a question of
definitions, but a table without primary key is usually impossible to do
any serious operations on.
>AND her is what i want
>
> Continent Country Ci
ty
>
>Europe
>Europe Norway
>Europe Norway Oslo
> Europe Norway Bergen
> Europe Norway Trondh
eim
>Europe Sweden
> Europe Sweden Stockh
olm
> Europe Sweden Gotabo
rg
>Europe Denmark
> Europe Denmark Kopen
hagen
> Europe Denmark Aalbo
rg
>Europe Germany
> Europe Germany Hambu
rg
> Europe Germany Munch
en
> Europe Germany Hanno
ver
>Asia
>Asia Japan
>Asia Japan Tokyo
> Asia Japan Hirroshim
a
>Asia Kina
>Asia Kina Shanghai
>Africa Kina
>Africa South-Africa
>Africa South-Africa Pretoria
>
>How do i that?
You don't. The data above has no key either, so it's not a relational
table. Consider moving the data to some properly normalized tables:
CREATE TABLE Countries
(Country varchar(20) NOT NULL,
Continent varchar(10) NOT NULL,
PRIMARY KEY (Country)
)
CREATE TABLE Cities
(City varchar(25) NOT NULL,
Country varchar(20) NOT NULL,
PRIMARY KEY (City),
FOREIGN KEY (Country) REFERENCES Countries (Country)
)
INSERT INTO Countries (Country, Continent)
VALUES ('Norway', 'Europe')
INSERT INTO Countries (Country, Continent)
VALUES ('Sweden', 'Europe')
.....
INSERT INTO Countries (Country, Continent)
VALUES ('South-Africa', 'Africa')
INSERT INTO Cities (City, Country)
VALUES ('Oslo', 'Norway')
INSERT INTO Cities (City, Country)
VALUES ('Bergen', 'Norway')
.....
INSERT INTO Cities (City, Country)
VALUES ('Pretoria', 'South-Africa')
The input needed to get the listing you describe above would be returned
by
SELECT co.Continent, co.Country, ci.City
FROM Countries AS co
INNER JOIN Cities AS ci
ON ci.Country = co.Country
ORDER BY co.Continent, co.Country, ci.City
The final reformatting to get it displayed exectly as above (including
the repeating of some lines with partial blank data) should be handled
by the presentation tier.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
|