SQL Server query to find all routes from city A to city B with transfers

Right, you provided working general SQL solution for all RDBs. I see you had here one hint – SQL Server. It supports recursive CTE which can be used to solve this task.

with RoutesCTE as(    select CAST([From] + '->' + [To] as nvarchar(max)) as [Route]          ,0 as TransfersCount          ,[From]          ,[To]    from Routes    union all    select r.[Route] + '->' + r1.[To]          ,TransfersCount + 1          ,r.[From]          ,r1.[To]    from RoutesCTE r        join Routes r1            on r.[To] = r1.[From]                and r1.[To] <> r.[From]                   and PATINDEX('%'+r1.[To]+'%', r.[Route]) = 0)select [Route]from RoutesCTE where [From] = 'Los Angeles'    and [To] = 'London'    and TransfersCount <= 2

So, here you have general solution for SQL Server and you can filter them by transfers count.

Personally ... I would go with CTE in this example, but also this could be done with dynamic SQL, as you don't know how much stops some person is willing to go for, make a function and dynamic SQL will dot his for you and do as many joins as it's needed, now there could be more formatting and adding those arrows and stuff ... but seems it works like this

And you you can execute @sql into temporary table and have another where condition for stop London

if object_id('tempdb..#Test') is not null drop table #Testcreate table #Test ([From] nvarchar(20), [To] nvarchar(20))insert into #Test ([From], [To])values ('Log Angeles', 'London'),('Log Angeles', 'New York'),('New York', 'London'),('Log Angeles', 'Seattle'),('Seattle', 'Paris'),('Paris', 'London')declare @StopsCount int = 2declare @beginingStop int = 2declare @sqlHeader nvarchar(max) = 'select t' + cast(@StopsCount as nvarchar) +                                '.[From], t' + + cast(@StopsCount as nvarchar) +                                '.[To] 'declare @sqlQuery nvarchar(max) = 'from #Test t' + cast(@StopsCount as nvarchar)while @StopsCount > 0BEGIN    set @StopsCount = @StopsCount - 1    set @sqlQuery = @sqlQuery + ' left join #Test t' + cast(@StopsCount as nvarchar) +                  ' on t' + cast(convert(int, (@StopsCount + 1)) as nvarchar) + '.[To]' +                  ' = t' + cast(@StopsCount as nvarchar) + '.[From]'                          set @sqlHeader = @sqlHeader + ', t' + cast(@StopsCount as nvarchar) + '.[To]'ENDset @sqlQuery = @sqlHeader + @sqlQuery + ' where t' + cast(@beginingStop as nvarchar) + '.[From] = ''Log Angeles'''execute (@sqlQuery)

Thanks for that good question.

This answer is not for specific RDBMS of OP (not SQL Server) but I write specific for Oracle that I'm familiar with.

The query uses Hierarchical Query instead of recursive CTE, with LEVEL <= 3 equal to TransfersCount <= 2 in SQL Server

WITH routes AS(    SELECT 'Los Angeles' from_place, 'London' to_place FROM DUAL UNION ALL    SELECT 'Los Angeles', 'New York' FROM DUAL UNION ALL    SELECT 'New York', 'London' FROM DUAL UNION ALL    SELECT 'Los Angeles', 'Seattle' FROM DUAL UNION ALL    SELECT 'Seattle', 'Paris' FROM DUAL UNION ALL    SELECT 'Seattle', 'Los Angeles' FROM DUAL UNION ALL    SELECT 'Paris', 'London' FROM DUAL )SELECT SUBSTR(SYS_CONNECT_BY_PATH(from_place , '->'), 3) || '->' || to_place AS pathFROM routesWHERE to_place = 'London'START WITH from_place = 'Los Angeles'CONNECT BY NOCYCLE  PRIOR to_place = from_place     AND to_place <> 'Los Angeles'     AND LEVEL <= 3 ;