1

Topic: Select or another?

There is table TBL1:
FLD1, FLD2
1, A
1, B
2, A
2, A C
3, D
3, E
4, A C
4, F
It is necessary to receive from TBL1 sampling of the following content:
FLD3, FLD4
1, A
1, B
1, A C
1, F
2, D
2, E
Whether it is possible to receive such result SQL Select query or here any other methods are necessary?
Direct, please, in what direction to search for the decision?

2

Re: Select or another?

sg729;
To fasten with heavy drugs. Other decision I do not see

3

Re: Select or another?

sg729 wrote:

Whether it is possible to receive such result SQL Select query...?

It is possible.

4

Re: Select or another?

Thanks!

5

Re: Select or another?

Matroskin wrote:

it is passed...
It is possible.

Nested queries? So after all links are in advance not known how many can be in a chain of communications on key FLD1:
A-> a C-> F... Etc.

6

Re: Select or another?

sg729 wrote:

it is passed...
Nested queries? So after all links are in advance not known how many can be in a chain of communications on key FLD1:
A-> a C-> F... Etc.

Put. Also fasten with drugs.
And is even better explain plainly that is necessary to you? Algorithm what?

7

Re: Select or another?

Vorontsov wrote:

it is passed...
Put. Also fasten with drugs.
And is even better explain plainly that is necessary to you? Algorithm what?

To learn all communications, for example, site A with others - both direct and indirect (through the intermediate nodes).
P.S.
Drugs never used-)

8

Re: Select or another?

sg729 wrote:

to Learn all communications, for example, site A with others - both direct and indirect (through the intermediate nodes).

Here to begin with, give please,
1. Determinations of concepts "node" and "the Node X is knotted with Y"
2. What communication of the concepts defined by you with contents of tables.

sg729 wrote:

P.S.
Drugs never used-)

It is good... It really - without everyone  - is good...
It is bad that it is imperceptible at once.

9

Re: Select or another?

yah wrote:

it is passed...
Here to begin with, give please,
1. Determinations of concepts "node" and "the Node X is knotted with Y"
2. What communication of the concepts defined by you with contents of tables.

Node - the object stored in a database. Each object has a unique identifier "A", "B", "C" etc. (for simplification of perception of an essence I write here single letter, actually there varchar (40). Some objects (all is not mandatory) are connected with each other. Communications are set in table TBL1 by the records having identical values of key field FLD1. I.e. the node "A" directly is knotted with "B" (through key FLD1=1) and with a node "C" (through key FLD1=2). In turn the node "C" directly is knotted with "F" (through key FLD1=4). Thus indirectly the node "A" is connected and with "F" (through "With").
The task simple - if needed to pull out from TBL1 for a specific node (for example for "And") identifiers of all other nodes expressly or by implication connected with set (i.e. with "And"). To transverse on TBL1 from top to down - not a variant as the table will contain some tens (possibly and hundreds) thousand records. Whether it is possible here artful Select - speak yes, but I yet do not know what. Perhaps such structure of the table does not suit at all this task, but could not invent is better.

10

Re: Select or another?

sg729 wrote:

Perhaps such structure of the table does not suit at all this task, but could not invent is better.

Well in general yes... It looks oddish, as generated separate comments.
Actually you described the equivalence relation on set of nodes.
There is a simple theorem that  <=> set it is possible to break into not intersected subsets in which elements are equivalent, and any two elements from different - is not present.
Actually at you  - if it is precisely described - on creation of components of connectivity for the graph.
For storage such  something more approaches like:

CREATE TABLE Knot_Objects (Knot_Id VARCHAR (40), Group_id INT)
CREATE TABLE Knot_Links (link_Id INT, from_Knot_Id VARCHAR (40), to_Knot_Id VARCHAR (40), link_Type int)
INSERT INTO Knot_Objects VALUES (' a ', null)
INSERT INTO Knot_Objects VALUES (' b ', null)
INSERT INTO Knot_Objects VALUES (' c ', null)
INSERT INTO Knot_Objects VALUES (' d ', null)
INSERT INTO Knot_Objects VALUES (' e ', null)
INSERT INTO Knot_Objects VALUES (' f ', null)
INSERT INTO Knot_links VALUES (1, ' a ', ' b ', 0)
INSERT INTO Knot_links VALUES (2, ' a ', ' with ', 0)
INSERT INTO Knot_links VALUES (3, ' d ', ' e ', 0)
INSERT INTO Knot_links VALUES (4, ' c ', ' f ', 0)
SELECT * FROM Knot_Objects AS ko
SELECT * FROM Knot_Links AS kl

Well and procedure it is possible to fill Group_id
Procedure - the cursor on Knot_Objects for example.
I.e. if edges of the graph - not quickly changing essence, such variant even is more desirable than any determination on the fly - as though it not to write.
About a question
Whether

sg729 wrote:

It is possible here artful Select - speak yes, but I yet do not know what.

At certain additional requirements to relation Knot_links, is in  the constructions, allowing to bypass hierarchy which it is possible  in as . But I badly know it.

11

Re: Select or another?

sg729;
It is tree search. The answer strongly depends on a specific DBMS which you use. The general answer is it is possible to make by means of one request, but it is not always reasonable and effective. Declared ten thousand records can be a problem, and can and not to be. The present problem is the tree and information storage methods about it. There are some approaches to it, literatures in a network the sea. Search, adapt.... The circuit Specified by you evidently does not correspond to the declared task of usage. If operation "to get all communications of a node" frequent and critical on time it is necessary to tinker. Store the full tree, trace its filling by means of triggers-procedures and get the necessary communications quickly. If operation rare and the user can wait - write request/procedure for search of a tree and there will be to you a happiness.

12

Re: Select or another?

waszkiewicz wrote:

sg729;
To fasten with heavy drugs. Other decision I do not see

The author still flattens)

13

Re: Select or another?

yah wrote:

set it is possible to break into not intersected subsets in which elements are equivalent, and any two elements from different - is not present

That's it it also is necessary for me! Something Was at the tip of the tongue similar, but could not seize an essence in any way. Now is clear where to row. Obviously you are right, to generate a tree of communications easier and to store it in basis, modifying as required. Thanks!-)

14

Re: Select or another?

Vorontsov wrote:

sg729;
Store the full tree, trace its filling by means of triggers-procedures and get the necessary communications quickly.

Perhaps and I will make. For some reason thought that at such approach for removal of the selected communication it will be difficult to implement modification of a tree without its full evolution (that would occupy a lot of time), but it appeared that it is possible to find the comprehensible decision.

15

Re: Select or another?

the Beaver wrote:

it is passed...
The author still flattens)

I am really similar to the madman?-) and however,  for health, laughter prolongs life-)

16

Re: Select or another?

sg729 wrote:

it is passed...
That's it it also is necessary for me! Something Was at the tip of the tongue similar, but could not seize an essence in any way. Now is clear where to row. Obviously you are right, to generate a tree of communications easier and to store it in basis, modifying as required. Thanks!-)

enjoy

17

Re: Select or another?

yah wrote:

enjoy

It is very grateful!-)

18

Re: Select or another?

sg729 wrote:

it is passed...
To learn all communications, for example, site A with others - both direct and indirect (through the intermediate nodes).
P.S.
Drugs never used-)

YES IT IS FINE!!!