Re: hierarchy structure

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.


>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
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.

Relevant Pages

  • Re: Time to talk about Global Warming [was Re: Maccies arent fanatical? (by the way, what is a "Macc
    ... Yes it does depend on how one defines freedom. ... Discharge a firearm in the city and you'll get ... In the country, just put one up. ... cities can do that people living in rural areas can't. ...
    (comp.sys.mac.advocacy)
  • Re: My Hurricane Katrina Predictions
    ... >country on the planet. ... That's about the limit of food stocks in the cities. ... had too short a history to think that the disaster would ever repeat. ... Our public educational system would be first rate, ...
    (talk.origins)
  • Re: Italy Itinerary
    ... Use trains if you plan to visit major cities. ... If instead you plan to tour mainly country locations, a car could be ...
    (rec.travel.europe)
  • Re: Persisting Information across forms
    ... Country displays the selected record from ... > transfers them to the Cities page (in this case a listing of cities filtered ... > list which brings them to the City record. ... > TransferParameters) which holds the parameters I want to pass along. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Italy Itinerary
    ... Use trains if you plan to visit major cities. ... If instead you plan to tour mainly country locations, a car could be ... mountains, for 45 per person per day you can get a gorgeous full board ...
    (rec.travel.europe)