1

Topic: What most simple method to construct a tree?

Like a standard situation, on an example 2 tables of the countries and cities.

ID Description
1 Russia
2 Belarus
ID OwnerID Description
1 1 Moscow
2 1 St.-Petersburg
3 2 Minsk
4 1 Vladivostok
5 2 Vitebsk

What most simple method to create a tree from 3 columns?

ID, OwnerID, Description

2

Re: What most simple method to construct a tree?

Valery_B;
Here probably any secret sense in ...

3

Re: What most simple method to construct a tree?

TaPaK;
No...

4

Re: What most simple method to construct a tree?

Valery_B wrote:

TaPaK;
No...

Give I will guess...
[spoiler so?]

with
country as
(select 1 [ID], ' Russia ' [Description] union all
select 2 [ID], ' Belarus ' [Description]
)
;
cities as
(
select 1 [ID], 1 [COUNTRY_ID], ' Moscow ' [Description] union all
select 2 [ID], 1 [COUNTRY_ID], ' St. Petersburg ' [Description] union all
select 3 [ID], 2 [COUNTRY_ID], ' Minsk ' [Description] union all
select 4 [ID], 1 [COUNTRY_ID], ' Vladivostok ' [Description] union all
select 5 [ID], 2 [COUNTRY_ID], ' Vitebsk ' [Description]
)
select
c. ID
,c. [Description] [Country]
,cs. [Description] [City]
from country c
join cities cs on c. ID=cs. COUNTRY_ID

[/spoiler]

5

Re: What most simple method to construct a tree?

komrad;
No, as a tree here also does not smell.

6

Re: What most simple method to construct a tree?

Valery_B wrote:

komrad;
No, as a tree here also does not smell.

So as it did not smell and directed by the task wink
Not the tree can is necessary for the author, and a bush...

7

Re: What most simple method to construct a tree?

Such table on an output should turn out:

ID OwnerID Description
1 NULL Russia
2 1 Moscow
3 1 St.-Petersburg
4 NULL Belarus
5 4 Minsk
6 1 Vladivostok
7 4 Vitebsk

Or such

ID OwnerID Description
1 NULL Russia
2 1 Moscow
3 1 St.-Petersburg
4 1 Vladivostok
5 NULL Belarus
6 5 Minsk
7 5 Vitebsk

8

Re: What most simple method to construct a tree?

Valery_B;
Wood is direct....
UNION ALL

9

Re: What most simple method to construct a tree?

TaPaK;
Precisely not the Union.
At Moscow and Russia will be identical =1

10

Re: What most simple method to construct a tree?

Valery_B wrote:

TaPaK;
Precisely not the Union.
At Moscow and Russia will be identical =1

Log  release 2

;with
country as
(select 1 [ID], ' Russia ' [Description] union all
select 5 [ID], ' Belarus ' [Description]
)
;
cities as
(
select 1 [ID], 1 [COUNTRY_ID], ' Moscow ' [Description] union all
select 2 [ID], 1 [COUNTRY_ID], ' St. Petersburg ' [Description] union all
select 3 [ID], 5 [COUNTRY_ID], ' Minsk ' [Description] union all
select 4 [ID], 1 [COUNTRY_ID], ' Vladivostok ' [Description] union all
select 5 [ID], 5 [COUNTRY_ID], ' Vitebsk ' [Description]
), groups as
(
SELECT
c. Id;
[OwnerId] = c. Id;
c. Description
FROM country c
UNION ALL
SELECT
c. Id;
c. COUNTRY_ID;
c. Description
FROM cities c
), bred as
(
SELECT
[Id] = ROW_NUMBER () OVER (ORDER BY OwnerId);
[OriginId] = Id;
OwnerId;
Description
FROM groups
)
SELECT
a. Id;
[OwnerId] = b. Id;
a. Description
FROM
bred a
LEFT JOIN
bred b
ON
a. OwnerId IS NOT NULL AND
b. OwnerId = b. Id AND
b. OriginId = a. OwnerId

11

Re: What most simple method to construct a tree?

;

wrote:

And generally a main point - "?". There are two different essences - the countries and cities,  them  to mix in a tree?

In our log of such questions do not set

12

Re: What most simple method to construct a tree?

And so does not go?
select-OwnerID as ID, OwnerID....
union all
....

13

Re: What most simple method to construct a tree?

KRS544 wrote:

And so does not go?
select-OwnerID as ID, OwnerID....
union all
....

Certainly goes, you in titles to write down?

14

Re: What most simple method to construct a tree?

TaPaK, yes bosh wrote
Here so

select-ID as ID, NULL/*ili-ID*/as OwnerID. from the Country
union all
select ID,-OwnerID as OwnerID. from the City

15

Re: What most simple method to construct a tree?

KRS544;
The main thing what the previous fillers  and republics in a minus did not climb yes?

16

Re: What most simple method to construct a tree?

TaPaK, that type
But the most simple method as asked smile

17

Re: What most simple method to construct a tree?

KRS544 wrote:

TaPaK, that type
But the most simple method as asked smile

The fastest method it not to suffer

18

Re: What most simple method to construct a tree?

Valery_B;
[spoiler then so?]

with
country as
(select 1 [ID], ' Russia ' [Description] union all
select 2 [ID], ' Belarus ' [Description]
)
;
cities as
(
select 1 [ID], 1 [COUNTRY_ID], ' Moscow ' [Description] union all
select 2 [ID], 1 [COUNTRY_ID], ' St. Petersburg ' [Description] union all
select 3 [ID], 2 [COUNTRY_ID], ' Minsk ' [Description] union all
select 4 [ID], 1 [COUNTRY_ID], ' Vladivostok ' [Description] union all
select 5 [ID], 2 [COUNTRY_ID], ' Vitebsk ' [Description]
)
, data as
(
select NULL [Parent], ID, [Description] [Val] from country
union all
select [ID], [COUNTRY_ID], [Description] [Val] from cities
)
select
ROW_NUMBER () over (order by d1.ID, d1.Parent) [ID]
,d1.Parent [OwnerID]
,d1.Val [Description]
from data d1
join data d2 on d1.ID=d2.Parent
order by d1.ID, d1.Parent

[/spoiler]

19

Re: What most simple method to construct a tree?

komrad wrote:

Valery_B;
[spoiler then so?]

with
country as
(select 1 [ID], ' Russia ' [Description] union all
select 2 [ID], ' Belarus ' [Description]
)
;
cities as
(
select 1 [ID], 1 [COUNTRY_ID], ' Moscow ' [Description] union all
select 2 [ID], 1 [COUNTRY_ID], ' St. Petersburg ' [Description] union all
select 3 [ID], 2 [COUNTRY_ID], ' Minsk ' [Description] union all
select 4 [ID], 1 [COUNTRY_ID], ' Vladivostok ' [Description] union all
select 5 [ID], 2 [COUNTRY_ID], ' Vitebsk ' [Description]
)
, data as
(
select NULL [Parent], ID, [Description] [Val] from country
union all
select [ID], [COUNTRY_ID], [Description] [Val] from cities
)
select
ROW_NUMBER () over (order by d1.ID, d1.Parent) [ID]
,d1.Parent [OwnerID]
,d1.Val [Description]
from data d1
join data d2 on d1.ID=d2.Parent
order by d1.ID, d1.Parent

[/spoiler]

And the last line with order by superfluous, by the way...

20

Re: What most simple method to construct a tree?

komrad;
select 6 [ID], ' Krokozhija ' [Description]

21

Re: What most simple method to construct a tree?

komrad is better I would not fulfill your request smile

22

Re: What most simple method to construct a tree?

TaPaK wrote:

komrad;
select 6 [ID], ' Krokozhija ' [Description]

There is no such country
But even if is,
We change this fragment

from data d1
join data d2

On

from data d1
LEFT join data d2

23

Re: What most simple method to construct a tree?

TaPaK wrote:

komrad is better I would not fulfill your request smile

I resolve, do not fulfill! smile

24

Re: What most simple method to construct a tree?

komrad;
All is more cheerful and more cheerful

wrote:

there is no such country
But even if is,

Speech about record of the country without cities, we after all not  here learn that at all all would be smile

25

Re: What most simple method to construct a tree?

komrad wrote:

it is passed...
I resolve, do not fulfill! smile

I.e. Minsk now belongs St. Petersburg, and all that result of one script...  the Pattern sighed