title: SQL Server query to find all routes from city A to city B with transfers
body: <p>I was asked this question while interviewing for developer position.</p>
  
  <p><strong>Task</strong>: Having flight routes stored in SQL Server table write query which will find all routes from city A to city B without transfer, with one or two transfers. For instance you have routes:</p>
  
  <pre><code>| From         | To
  ----------------------
  Los Angeles     London   
  Los Angeles     New York
  New York        London
  Los Angeles     Seattle
  Seattle         Paris
  Paris           London
  </code></pre>
  
  <p>And you need to find all routes with transfers from Los Angeles to London. The result should be like this</p>
  
  <pre><code>Route
  ------------------------
  Los Angeles-&gt;London
  Los Angeles-&gt;New York-&gt;London
  Los Angeles-&gt;Seattle-&gt;Paris-&gt;London
  </code></pre>
  
  <p>My solution was this </p>
  
  <pre class="lang-sql prettyprint-override"><code>select [From] + '-&gt;' + [To] as [Route] from Routes where [From] = 'Los Angeles' and [To] = 'London'
  union 
  select r1.[From] + '-&gt;' + r1.[To] + '-&gt;' + r2.[To] as [Route] from Routes as r1 
  join Routes as r2 on r1.[To] = r2.[From]
  where r1.[From] = 'Los Angeles' and r2.[To] = 'London'
  union 
  select r1.[From] + '-&gt;' + r1.[To] + '-&gt;' + r2.[To] + '-&gt;' + r3.[To] as [Route] from Routes as r1 
  join Routes as r2 on r1.[To] = r2.[From]
  join Routes as r3 on r2.[To] = r3.[From]
  where r1.[From] = 'Los Angeles' and r3.[To] = 'London'
  </code></pre>
  
  <p>Works but looks not very good and if we need to find routes with 3, 4, 5 or more transfers we need to add new unions with more complex selects. </p>
  
  <p>For this particular example it’s good because the people as a rule are not interested in flights with more than 2 transfers. But in general I think this query can look better. Perhaps someone can solve this task with more general query. Thanks.</p>