1

Topic: Whether it is possible to make a recursion in a SQL query?

The circuit simplified, but as a whole so:
There is a table "a load card", characterizing a load and its driving. The given card can be type "car", "coach" and "warehouse".
Weeding, say, the such:

id;
id_ - The load (the link to the same card) whence came;
Type (' auto ', ' wagon ', ' placement ');
id_ Either id_ or id_;
Load weight;

For example, any load arrived the car, then was moved on a warehouse. It is required to define transport on which there arrived a load. It is simple - we have a load card in a warehouse, we address to a field id_ (it is the link to the same card) and we look its type - ' auto '. Across the field id_ we define the car.
But the situation becomes complicated, if a load some times moved from a warehouse on a warehouse the chain of cards where the car card, then some cards of a warehouse to last will be the first, accordingly, is formed. And it is necessary to define the car which there arrived a load initially. And here it is necessary to untwist all chain, yet you will not meet a vehicle.
Whether it is possible to make it by means of storable function or procedure? And how to combine a procedure call in a SQL query if for example there the list of cards of a warehouse, and it is necessary to deduce the list of vehicles for them - any will be defined at once, and others need to be pulled out through a recursion, and to produce this all one list.

2

Re: Whether it is possible to make a recursion in a SQL query?

Look here at it

3

Re: Whether it is possible to make a recursion in a SQL query?

Would not prevent unique  a load. And if id cards autoincremental (allows to sort cards as relocation), the task becomes trivial.

4

Re: Whether it is possible to make a recursion in a SQL query?

982183;
At once I will tell, actually the structure is a bit more difficult, than a tree the parent-descendant. On one card of a warehouse many cards of coaches and a car can come. I.e. structure not one-to-many, and many-to-much where there are 3 tables:
1
2
_ (___1_id, ___2_id).

5

Re: Whether it is possible to make a recursion in a SQL query?

6

Re: Whether it is possible to make a recursion in a SQL query?

svnvlad wrote:

at once I will tell, actually the structure is a bit more difficult, than a tree the parent-descendant. On one card of a warehouse many cards of coaches and a car can come.

Well so you at first deduce a chain cargo_card
And then already look what HARDWARE took part in members of this chain.
You gave structure of the primary information.
The code you will draw structure of the necessary report also you it becomes more clear.

7

Re: Whether it is possible to make a recursion in a SQL query?

svnvlad wrote:

And it is necessary to define the car which there arrived a load initially.

Well so for this purpose at first it is necessary to define the first cargo_card this load.

svnvlad wrote:

And here it is necessary to untwist all chain, yet you will not meet a vehicle.

And here it is not clear. There are loads without the HARDWARE?

8

Re: Whether it is possible to make a recursion in a SQL query?

svnvlad wrote:

it is necessary to untwist all chain, yet you will not meet a vehicle.

Implementation depends first of all on the server version. If it is 8 + - that carried, it supports recursive CTE, and it is possible to implement all one request. If is not present - only stored procedure.
Whether

svnvlad wrote:

It is possible to make it by means of storable function or procedure?

Yes, it is possible. Only procedure. Functions in MySQL cannot be recursive.

svnvlad wrote:

how to combine a procedure call in a SQL query if for example there the list of cards of a warehouse, and it is necessary to deduce the list of vehicles for them - any will be defined at once, and others need to be pulled out through a recursion, and to produce this all one list.

Procedure cannot be used as request data source. It can be used only as independent request - receive from it ready record set or to form in it (temporal) table with the prepared data which then to use in request. The first approach is more often used.

9

Re: Whether it is possible to make a recursion in a SQL query?

982183 wrote:

it is passed...
Well so for this purpose at first it is necessary to define the first cargo_card this load.
it is passed...
And here it is not clear. There are loads without the HARDWARE?

There are the loads moved from a warehouse on other warehouse. But it is primary on the first warehouse it comes a vehicle. It is necessary to untwist to the beginning, on what vehicle it got on the first warehouse.
It is necessary, that in the upper grid there were warehouse cards, and in lower - detail - the list of vehicles with which this load arrived. The previous warehouse, namely transport should be displayed not. Though on structure the warehouse is equivalent to transport.

10

Re: Whether it is possible to make a recursion in a SQL query?

982183 wrote:

it is passed...
Well so you at first deduce a chain cargo_card

The question in that also consists how to deduce to (untwist) this chain. It is a recursion.

11

Re: Whether it is possible to make a recursion in a SQL query?

The task essence is that: on a picture a rectangle "the Entering HARDWARE" - to display vehicles. Not the same that "Entering sources".

12

Re: Whether it is possible to make a recursion in a SQL query?

svnvlad wrote:

the Question in that also consists how to deduce to (untwist) this chain. It is a recursion.

982183 wrote:

Look here at it

13

Re: Whether it is possible to make a recursion in a SQL query?

That you name "a load card"
Most likely there is "a delivery stage"
According to you it is necessary having on a request input finite "a delivery stage" / "a load card"
. cargo_card.id
To receive all previous id from transhipment.from_cargo_card_id
And then to pick up characteristics of transport from appropriate tables.

14

Re: Whether it is possible to make a recursion in a SQL query?

Another matter that at stages happens join and uncoupling of batches is obvious.
Concept "nomenclature" I at you do not see.
And as it is implemented - yet I do not understand.

15

Re: Whether it is possible to make a recursion in a SQL query?

On the last picture you solve the task
"What transport participated in delivery", instead of that transport which was in the first stage

wrote:

And it is necessary to define the car which there arrived a load initially

Anyway it is necessary to define at first the list of stages, and then to pick up transport from appropriate tables.

16

Re: Whether it is possible to make a recursion in a SQL query?

982183;
The load card is a table cargo_card on the yellow circuit. The mention of the nomenclature of a load goes through a field contract_cargo_sub_id - there a long sheaf of tables which eventually leads cargo_nomenclature. But it is not important. As well as what transport. All question - how to organize technically a recursion cargo_card.id-> trasshimpent.to_cargo_card_id-> transshipment.from_cargo_card_id-> cargo_card.id-> trasshimpent.to_cargo_card_id-> transshipment.from_cargo_card_id-> etc.
Until will be cargo_card.object_type IN (' auto ', ' wagon ').

17

Re: Whether it is possible to make a recursion in a SQL query?

982183 wrote:

it is passed...
it is passed...

I transversed it, in the same place is described for a tree case when one parent and is a lot of successors. And here on the contrary, more likely, it is a lot of parents and some successors (many-to-much). Though I will try to adapt, of course, those procedures for many-to-much.

18

Re: Whether it is possible to make a recursion in a SQL query?

svnvlad wrote:

And here on the contrary, more likely, it is a lot of parents and some successors (many-to-much).

wrote:

at once I will tell, actually the structure is a bit more difficult, than a tree the parent-descendant. On one card of a warehouse many cards of coaches and a car can come. I.e. structure not one-to-many, and many-to-much where there are 3 tables:

There are no at you many-to-much in described process.

wrote:

1
2
_ (___1_id, ___2_id).

Consider the table of communications as the unique table describing hierarchy.
Probably turned (if I correctly understood)
(If you have a join of batches)
If is and  batches yes, there are many to many.
But from the second picture it does not follow.
And the table of "cards" and the HARDWARE as additional two-level reference manuals.

19

Re: Whether it is possible to make a recursion in a SQL query?

And the recursion was considered here: