CODEWITHSUNDEEP
sqlhierarchyrecursive-queryconnect-by
WalkingCoffeeMan
WalkingCoffeeMan

Reputation: 109

SQL Recursive Queries , problems with understanding "prior" expression on connect by clause

I created a table like the following , to understand recursive queries:

enter image description here

enter image description here

For this I made an insert, to cause a loop to the query and now my table looks like the following (Athens to Vienna connection has been added):

enter image description here

Now to make an uncycled recursive query , I used the connect by function and wrote the code below:

select distinct abflug,ankunft,level from flugverbindungen
start with ABFLUG = 'Wien'
connect by nocycle prior ankunft =  abflug
order by level;

This result came out :

enter image description here

I can see that the query ran till Vienna and just ended on Pressburg. But when i change the prior expression from ankunft to abflug like this:

select distinct abflug,ankunft,level from flugverbindungen
    start with ABFLUG = 'Wien'
    connect by nocycle  ankunft = prior abflug
    order by level;

I get the following result:

enter image description here

Now Athens to Vienna has level 2 , which is very strange , because the root node should be Vienna and not Athens. I also don't understand , how Laibach to Belgrad became level 4.

In conclusion I actually don't understand what the prior expression changes on the query and what it is good for. I would really appreciate it , when you would explain the prior expression with this example. What is actually changing when i swap the side of the prior expression ?

Upvotes: 0

Views: 223

Answers (2)

WalkingCoffeeMan
WalkingCoffeeMan

Reputation: 109

As we know recursive queries are calling themselves and we always take over a parents column from the row before. With prior we are defining which column we want to take over from the row before in our recursive query. In my case I am always taking the arrivals column( ankunft) and change it to my new departure so I have to use ankunft as prior column. Otherwise the results will not be correct semantically, because we wanna simulate an airplane which is flying through the stations , beginning from a station we define (in my case Wien).

Thanks to The Impaler for trying to help me though.

Upvotes: 0

The Impaler
The Impaler

Reputation: 48810

You can use a Recursive CTE to walk the graph. For example:

with
c (abflug, ankunft, lvl) as (
  select abflug, ankunft, 1 from t where abflug = 'Wien'
  union all
  select t.abflug, t.ankunft, c.lvl + 1
  from c
  join t on t.abflug = c.ankunft and c.lvl <= 4
)
select * from c;

Result:

ABFLUG   ANKUNFT    LVL
-------  ---------  ---
Wien     Pressburg    1
Wien     Laibach      1
Laibach  Paris        2
Laibach  Belgrad      2
Belgrad  Athen        3
Athen    Wien         4
Wien     Pressburg    5
Wien     Laibach      5

See running example at db<>fiddle.

Upvotes: 1

Related Questions