CODEWITHSUNDEEP
sqlsql-serverrecursion
Dragon
Dragon

Reputation: 85

Understanding SQL Recursion

I have the following code

      SELECT 
            Customer,
            booking,
            [date],
            Parent_Booking,
            0 AS change_number
            FROM bookings

Lets imagine that I have a website on which customers can book room nights. For each booking made on the website there is a booking number created.

Example:

Current rows in my DB2 table:

Customer    booking#      date      Parent_Booking
1           11111         1.01.15   NULL

Now comes the important part. If the customer makes changes to his booking, the booking number related to his original booking would be frozen and a new booking number would be created. Lets say that the customer changed his booking 3 times. The rows in my table would look like this:

Customer    booking#      date      Parent_Booking
1           11111         1.01.15   NULL
1           22222         5.01.15   11111          -----> First Change
1           33333         6.01.15   22222          -----> Second Change
1           44444         10.01.15  33333          -----> Third Change

Now If I run the code above I will get only the first two lines:

Customer    booking#      date      Parent_Booking
1           11111         1.01.15   NULL
1           22222         5.01.15   11111          -----> First Change

I want to get all lines, meaning I want to use recursion and get all parents of a parents until there are no parents. How can I modify my main query to achieve this?

I think it needs to happen in this part:

ON        AL.[Parent Reservation No_]=DB2.[B_KEY]

I want to keep joining until the [Parent Reservation No.] = NULL

Upvotes: 0

Views: 242

Answers (1)

Paul Griffin
Paul Griffin

Reputation: 2436

Since you are using T-SQL, and your table is structured as an adjacency list, the solution you are looking for is a Recursive Common Table Expression:

WITH cte AS (
  SELECT 
    Customer,
    booking,
    [date],
    Parent_Booking,
    0 AS change_number
  FROM bookings
  WHERE Parent_Booking IS NULL

  UNION ALL 

  SELECT
    b2.Customer,
    b2.booking,
    b2.[date],
    b2.Parent_Booking,
    cte.change_number + 1 AS change_number
  FROM bookings b2
  INNER JOIN cte ON b2.Parent_Booking = cte.booking
)

SELECT * FROM  cte

Here's a SQLFiddle

The first part of the expression selects the "base" rows, those that have no parent, then the second part of the expression is joined recursively to the result set until there are no more results to join, or until 100 recursions are reached, whichever comes first.

The change_number column is one that I added to show how many times the customer has changed their booking, but it also illustrates how the recursion works. Rows with change_number = 0 are from the first recursion, rows with change_number = 0 or 1 are from the second recursion, and so on. Being able to build custom columns like this can become very useful for sorting or aggregating data as you recurse through a set, and I wanted to include a simple example of this capability.

Upvotes: 4

Related Questions