How to recursively retrieve towns available?-CodePudding

I am making a transportation application, and right now, I am implementing the SQL and writing some queries.

I have a table Train(From, To), which is basically a table that has two columns. The first column is the city you start at, and the next column is the city you arrive at.

My question is how can I retrieve all cities you can go to from a certain city recursively? For example, here is a sample dataset:

From	To
St Louis	Jefferson City
Jefferson City	Kirkwood
Chicago	St Louis
Kirkwood	Belleville

So if I passed in St. Louis into this recursive function, I would get Jefferson City, Kirkwood, and Belleville as reachable cities.

SQL language is Postgres/Sqlite :)

CodePudding user response：

Use WITH RECURSIVE:

WITH RECURSIVE
    c(t) AS (
        SELECT "to" FROM cities
            WHERE "from" = 'St Louis'
        UNION
        SELECT "to" FROM cities, c
            WHERE c.t = cities."from"
    )
SELECT t FROM c;

(Tested in sqlite3).