How to get data with recursive query-CodePudding

I want to write query finding all cities reachable from city Agat by train with a maximum of 3 stops. I am trying to solve this but I keep getting errors, not sure how to write this better?

tables look like this


CREATE TABLE CITIES (
  CID int primary key,
  CITY varchar(50) default NULL
);

CREATE TABLE Trains (
  departure integer references Cities(cid),
  arrival integer references Cities(cid),
  railline varchar(255) default NULL,
  Tid integer primary key,
  Price integer default NULL
);

Here's the recursive query and Im getting this error

ERROR:  operator does not exist: integer = character varying
LINE 6:   INNER JOIN trains t ON t.arrival = c.city

WITH recursive get_cities AS (
  SELECT 0 AS stops, city FROM cities WHERE city = 'Agat'
  UNION
  SELECT r.stops   1 AS stops, c.city
  FROM cities c
  INNER JOIN trains t ON t.arrival = c.city
  INNER JOIN get_cities r ON r.city = t.departure AND r.stops < 3
)

select * from get_cities;

CodePudding user response：

Some comments :

You have to explicit the parameters of the recursive query (see the manual) : WITH RECURSIVE get_cities (stops, city) AS ( ... )
In the query, you have to refer to the cid column of table city instead of the city column
Optional : The condition to stop the recursive should be in the WHERE clause of the recursive term : WHERE r.stops < 3
Optional : I would suggest to inverse the order of the tables in the JOIN clause starting with get_cities for more readability

Try this :

WITH RECURSIVE get_cities (stops, cid) AS (
  SELECT 0, cid
    FROM cities 
   WHERE city = 'Agat'
UNION ALL
  SELECT r.stops   1 AS stops, c.cid
    FROM get_cities r 
   INNER JOIN trains t ON t.departure = r.cid
   INNER JOIN cities c ON c.cid = t.arrival
   WHERE r.stops < 3
)
select * from get_cities;