sql server mseq:
[quoted text, click to view] 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 City
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.
[quoted text, click to view] >AND her is what i want
>
>Continent Country City
>
>Europe
>Europe Norway
>Europe Norway Oslo
>Europe Norway Bergen
>Europe Norway Trondheim
>Europe Sweden
>Europe Sweden Stockholm
>Europe Sweden Gotaborg
>Europe Denmark
>Europe Denmark Kopenhagen
>Europe Denmark Aalborg
>Europe Germany
>Europe Germany Hamburg
>Europe Germany Munchen
>Europe Germany Hannover
>Asia
>Asia Japan
>Asia Japan Tokyo
>Asia Japan Hirroshima
>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
--
