1

Topic: Help to solve such interesting task

It is necessary to find a circle of friends, the friend of my friend
The table example is specified more low;
It is necessary to find circles of friends in which there are general friends
THE SIDE 1 | FRIENDS........ | 1 | FRIENDS
______________________________________________________
CIRCLE 1........ | Boris, Artem | the Circle 2...... | Victor, Artem
______________________________________________________
CIRCLE 3........ | Denis, Evgenie | the Circle 4...... | Vladimir, Stanislav
______________________________________________________
CIRCLE 2........ | Victor, Artem | the Circle 5........ |, Victor
Apparently, from the Circle of 1 Artem is in the Circle 2
And thus from the Circle 2 Victor is in the Circle 5.
. the general friends Between circle 1 and around 2 through Artema, both Around 2 and Around 5 through Victor.
Accordingly Circles 1, the Circle 2 and the Circle 5 have the general friends.
It is necessary to write such  to deduce such circles of friends, and to consider that names of friends are partitioned by a comma
I hope could explain

2

Re: Help to solve such interesting task

I ask not to pay attention to a screenshot it not correct, here a corrected variant

3

Re: Help to solve such interesting task

In  on crookedly designed DB there is nothing interesting.

4

Re: Help to solve such interesting task

Dimitry Sibiryakov;
?

5

Re: Help to solve such interesting task

Mr. Brown wrote:

Toest?

Finish basis to the third normal form and your request becomes trivial WITH
RECURSIVE/CONNECT BY.

6

Re: Help to solve such interesting task

Dimitry Sibiryakov;
I ask to help, how to me to transform my data in 3nf.
As it approximately should look

7

Re: Help to solve such interesting task

Mr. Brown wrote:

I Ask to help, how to me to transform my data in 3nf.
As it approximately should look

You take the , you unpack on a paper.
At you 3 vertical strips on the printed.
You take scissors and you cut a leaf on an average vertical strip.
You take a part where at you "the side 2", and on the horizontal strip first on top you cut off.
Part where "the Side 2", you throw out.
You pray.
The remained slice you glue from below to a part with "the side 1" that circles were under circles, and friends under friends.
- you    !
Here so approximately "it" also should look.

8

Re: Help to solve such interesting task

Fogel wrote:

it is passed...
You take the , you unpack on a paper.
At you 3 vertical strips on the printed.
You take scissors and you cut a leaf on an average vertical strip.
You take a part where at you "the side 2", and on the horizontal strip first on top you cut off.
Part where "the Side 2", you throw out.
You pray.
The remained slice you glue from below to a part with "the side 1" that circles were under circles, and friends under friends.
- you    !
Here so approximately "it" also should look.

Thanks for humour)
It yes, understood)) but how to save correlation between circles?
In respect of that that they had communication among themselves.

9

Re: Help to solve such interesting task

Mr. Brown wrote:

Thanks for humour)
It yes, understood)) but how to save correlation between circles?
In respect of that that they had communication among themselves.

It was the satire.
Use force the Manhole a database.
Here a database which uses Mr. Brown.
And here the table which is stored in basis which uses Mr. Brown.
And here the data from a screenshot which are stored in the table which is stored in basis which uses Mr. Brown.
And here a command select which pulls the data that on a screenshot, but from the table which is stored in basis which uses Mr. Brown.
And here function substr which selects friends from circles which are produced by a command select which pulls the data that on a screenshot, but from the table which is stored in basis which uses Mr. Brown.
And here a condition connect by which installs communications of friends on circles which are received by function substr which selects friends from circles which are produced by a command select which pulls the data that on a screenshot, but from the table which is stored in basis which uses Mr. Brown.

10

Re: Help to solve such interesting task

Mr. Brown wrote:

it is passed...
It yes, understood))

On course Fogel too not especially understood that is 3nf...
That is shown, is not even 1nf

11

Re: Help to solve such interesting task

andrey_anonymous wrote:

it is passed...
On course Fogel too not especially understood that is 3nf...
That is shown, is not even 1nf

Fogel - not the reader, Fogel - the writer.

12

Re: Help to solve such interesting task

Mr. Brown;
[spoiler connected components: quick find quick union algorithm]

declare
type char_array is table of varchar2 (20) index by varchar2 (20);
type arr_elems is table of sys.ku $ _ vcnt index by varchar2 (20);
root char_array;
root_elems arr_elems;
n varchar2 (20);
l integer: = dbms_utility.get_time ();
procedure print (v in varchar2) is
begin
dbms_output.put_line (to_char ((dbms_utility.get_time-l)/100, ' 0999.99 ') || ' ' || v);
l: = dbms_utility.get_time ();
end;
function get_root (n varchar2) return varchar2 is
begin
if root.exists (n) then
return root (n);
else
return null;
end if;
end;
procedure update_root (old_root varchar2, new_root varchar2) is
i pls_integer;
begin
if old_root! =new_root then
root_elems (new_root): =root_elems (new_root) multiset union all root_elems (old_root);
for i in 1. root_elems (old_root).count
loop
root (root_elems (old_root) (i)): =new_root;
end loop;
root_elems (old_root).delete;
end if;
end;
procedure add_elem (p_root varchar2, p_elem varchar2) is
begin
if not root_elems.exists (p_root) then
root_elems (p_root): =sys.ku $ _ vcnt (p_elem);
else
root_elems (p_root).extend ();
root_elems (p_root) (root_elems (p_root).count): =p_elem;
end if;
end;
procedure add_link (p varchar2, q varchar2) is
r1 varchar2 (20);
r2 varchar2 (20);
new_root varchar2 (20);
begin
r1: = get_root (p);
r2: = get_root (q);
if r1 is null or r2 is null then
new_root: = coalesce (r1, r2, p);
if r1 is null then add_elem (new_root, p); root (p): =new_root; end if;
if r2 is null then add_elem (new_root, q); root (q): =new_root; end if;
else
new_root: = least (r1, r2);
root (p): =new_root;
root (q): =new_root;
update_root (greatest (r1, r2), new_root);
end if;
end;
begin
print (' start ');
for r in (
==> with v (p, q) as (
==> select ' the Circle 1 ', ' Boris ' from dual union all
==> select ' the Circle 1 ', ' Artem ' from dual union all
==> select ' the Circle 3 ', ' Denis ' from dual union all
==> select ' the Circle 3 ', ' Evgenie ' from dual union all
==> select ' the Circle 2 ', ' Victor ' from dual union all
==> select ' the Circle 2 ', ' Artem ' from dual union all
==> select ' the Circle 4 ', ' Vladimir ' from dual union all
==> select ' the Circle 4 ', ' Stanislav ' from dual union all
==> select ' the Circle 1 ', ' Andrey ' from dual union all
==> select ' the Circle 1 ', ' Victor ' from dual
==>)
==> select *
==> from v
)
loop
add_link (r.p, r.q);
end loop;
print (' processed ');
print (' groups: ');
n: = root_elems.first ();
while n is not null loop
for i in 1. root_elems (n).count loop
dbms_output.put (', ' || root_elems (n) (i));
end loop;
dbms_output.put_line (' ');
n: = root_elems.next (n);
end loop;
end;

[/spoiler]
In the selected you substitute the request dataful

13

Re: Help to solve such interesting task

xtender;
There it is necessary to steam of lines to comment out, that to group elements did not get

14

Re: Help to solve such interesting task

xtender wrote:

Mr. Brown;
..
In the selected you substitute the request dataful

At first the red ran white of wood, then the white ran red of wood.
Then the forester came and all expelled)))

15

Re: Help to solve such interesting task

xtender;
Thanks!
On pure SQL probably to solve?

16

Re: Help to solve such interesting task

Mr. Brown;
the Amount  communications in many to many

17

Re: Help to solve such interesting task

Mr. Brown;
Decision Elic'a

with test (clientid, accountid) as (
select ' the Circle 1 ', ' Boris ' from dual union all
select ' the Circle 1 ', ' Artem ' from dual union all
select ' the Circle 3 ', ' Denis ' from dual union all
select ' the Circle 3 ', ' Evgenie ' from dual union all
select ' the Circle 2 ', ' Victor ' from dual union all
select ' the Circle 2 ', ' Artem ' from dual union all
select ' the Circle 4 ', ' Vladimir ' from dual union all
select ' the Circle 4 ', ' Stanislav ' from dual union all
select ' the Circle 1 ', ' Andrey ' from dual union all
select ' the Circle 1 ', ' Victor ' from dual
)
select max (group_member_id) as group_max_id, clientid, accountid
from
(select clientid as group_member_id
, connect_by_root accountid as accountid
, connect_by_root clientid as clientid
from test
connect by nocycle decode (accountid, prior accountid, 1, 0)
+ decode (clientid, prior clientid, 1, 0)
= 1
)
group by accountid, clientid
order by group_max_id, clientid, accountid
;

18

Re: Help to solve such interesting task

Thanks for the decision as thus in to pull out that that in Basis to be stored in this type
CIRCLE 1 | Boris, Artem

19

Re: Help to solve such interesting task

Moreover and spoons.
Yes what there spoons - serving spoons!