Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Recursive Queries in MS SQL Server

Introduction
Infinite looping
Processing trees with recursive CTEs
Processing graphs with recursive ctes
33. Graphs – additional exercise
Summary

Instruction

Perfect! It's time for an exercise where you create the query all on your own!

Exercise

We have some new tables! The first, GermanCity, contains two columns, Id and Name. This time, we're traveling between cities in Germany and we won't be calculating the distance using a GPS function. Instead, we've been given a second table called Road that has the columns CityFrom, CityTo, and Time. It also comes with average trip durations on a given route from one city to another.

Let's travel between the four cities, starting in Berlin. Your task is to list the travel paths starting in Berlin and covering all four cities. Order the paths in descending order by TotalTime.

In your answer, provide the following columns:

  • Path – the city names, separated by N'-',
  • LastId – the ID of the last city visited,
  • TotalTime – the total time spent driving,
  • CountPlaces – the number of places visited; it should equal 4.

Stuck? Here's a hint!

This one can be tough, so here is a hint: in the recursive member, you will have to join the recursive table you create once with the Road table and twice with the table GermanCity (one INNER JOIN for CityFrom, the other one for CityTo). In the termination check, use CHARINDEX(x, Path) = 0 to make sure that city x has not been visited so far.