Arrange two tables and get result I have these two tables:

Center              

+--------+---+--------+--------+---------+
|IdParent|Row|IdCenter|IdNodeIn|IdNodeOut|
+--------+---+--------+--------+---------+
|1       |1  |470     |1       |5218     |
|1       |2  |22      |5298    |1        |
|1       |3  |20      |5242    |5284     |
+--------+---+--------+--------+---------+


Connection

+--------+---+------------+--------+----------+---------+
|IdParent|Row|IdCenterFrom|IdNodeIn|IdCenterTo|IdNodeOut|
+--------+---+------------+--------+----------+---------+
|1       |1  |470         |5218    |20        |5242     |
|1       |2  |20          |5284    |22        |5298     |


I need to get result like this:

+--+--------+------+
|Id|IdCenter|IdNode|
+--+--------+------+
|1 |470     |1     |
|2 |470     |5218  |
|3 |20      |5242  |
|4 |20      |5284  |
|5 |22      |5298  |
|6 |22      |1     |


Detail:

Center table has 3 rows (can have more, but minimum 2 rows) indicating 3 center, each has tow connection points

As part of network, input point in first center and output point in last center may connect to no node, so it's Id can be 1. Centers are not sorted.

Connection table shows that two connections has connected these three centers. Connection table is sorted, means that first row IdNodeIn is the first connection start point, then if goes to IdNodeOut on the same row ( show start and end of connection). Then this circuit continue to second row, and connect to IdNodeIn and the last end point is IdNodeOut.

So Center by Id = 470 is connected to 20 and then to 22.

Or by connection by Node Id is 5218 to 5242, then to 5284 and last to 5298

I want all of these in above result table, I can get them all in one table but I can not arrange them, here is my select statement:

SELECT 
    m.Id, m.IdCenter, m.IdNodeIn
FROM   
    Link.LinkCenter m
LEFT OUTER JOIN 
    Link.LinkConnection c ON c.IdNodeOut = m.IdNodeIn

UNION ALL

SELECT 
    m.Id, m.IdCenter, m.IdNodeOut
FROM   
    Link.LinkCenter m
LEFT OUTER JOIN 
    Link.LinkConnection c ON c.IdNodeIn = m.IdNodeOut
ORDER BY
    Id, IdC


and the result is:

+----+--------+------+
|Id  |IdCenter|IdNode|
+----+--------+------+
|NULL|470     |1     |
|1   |470     |5218  |
|NULL|22      |1     |
|2   |22      |5298  |
|1   |20      |5242  |
|2   |20      |5284  |


here is a graph:

+---------------+       +---------------+       +---------------+
|IdCenter : 470 |       |IdCenter : 20  |       |IdCenter : 22  |
|IdNodeIn : 1   |   +--&gt;|IdNodeIn : 5242|   +--&gt;|IdNodeIn : 5298|
|IdNodeOut: 5218|&gt;--+   |IdNodeOut: 5284|&gt;--+   |IdNodeOut: 1   |
+---------------+       +---------------+       +---------------+


Please help, although it may sound easy.
I'm not sure this is what are you looking for because IMHO you must deal with recursive functions.

Using only centers table

select IdCenter, IdNode
from
(
    select     ce.IdCenter, ce.IdNodeIn IdNode, 0 as nodeOrder, Row
    from       center ce
    union all
    select     ce.IdCenter, ce.IdNodeOut IdNode, 1 as nodeOrder, Row
    from       center ce
) x
order by Row, idCenter, nodeOrder;


Using connection table to set the correct order

with rowOrder as
(
    select    ce.IdCenter, ce.IdNodeIn, ce.IdNodeOut,
              case when co.Row is null then 3 else co.Row end Row 
    from      center ce
    left join connection co
    on        co.IdCenterFrom = ce.IdCenter
) 
select IdCenter, IdNode 
from (
       select IdCenter, IdNodeIn IdNode, Row from rowOrder
       union all
       select IdCenter, IdNodeOut IdNode, Row from rowOrder
     ) x
order by Row;


This is the final result on both cases:

| IdCenter | IdNode |
|----------|--------|
| 470      | 1      |
| 470      | 5218   |
| 22       | 5298   |
| 22       | 1      |
| 20       | 5242   |
| 20       | 5284   |


Rextester here