Article: Recursive SQL


08 November 2017
Download link: Click here

Although I've spent some time with SQL over the years for my websites that required payment processing (such as SilentAim), I was never really required to write complex queries. I decided to take a data models course in university which goes through SQL and XML in-depth. As part of a project, I wrote a recursive SQL query which finds all flight destinations from an origin; then the total cost is calculated. For instance:

BUF -> JFK -> LAX -> SEA
BUF -> BOS
BUF -> ORD
BUF -> JFK -> ORD


Using a common table expression (CTE), it's actually fairly straightforward to process all outcomes.





Some lines to notice include things to notice includes line 30 which sums the results together recursively, returning the final result at the end. Line 36 is the origin, changing this will generate a new table of origin to destination results. Running the above SQL query with the data provided returns the following result.