# Comments - Recursive Common Table Expressions Overview

10 months, 1 week ago

How to get the depth of the graph?

```WITH RECURSIVE graph as (
SELECT pr.*,@depth as depth FROM rcte_people_relationships pr WHERE source_id=3
UNION
SELECT pr.*,(@depth:=@depth+1) as depth FROM rcte_people_relationships pr, graph WHERE graph.target_id=pr.source_id
)
SELECT * FROM graph
CROSS JOIN (SELECT @depth := 0) AS depth;```

Creates an infinite loop.

7 months ago

From the documentation above, I've added n to indicate the level within the hierarchy:

```CREATE TABLE bus_routes (origin varchar(50), dst varchar(50));
INSERT INTO bus_routes VALUES
('New York', 'Boston'),
('Boston', 'New York'),
('New York', 'Washington'),
('Washington', 'Boston'),
('Washington', 'Raleigh')
;

WITH RECURSIVE paths (n, cur_path, cur_dest) AS (
SELECT 1 AS n, origin, origin FROM bus_routes WHERE origin='New York'
UNION
SELECT n + 1
, CONCAT(paths.cur_path, ',', bus_routes.dst)
, bus_routes.dst
FROM paths, bus_routes
WHERE paths.cur_dest = bus_routes.origin
AND LOCATE(bus_routes.dst, paths.cur_path) = 0
)
SELECT * FROM paths
;
```

7 months ago

The result:

```+---+-----------------------------+------------+
| n | cur_path                    | cur_dest   |
+---+-----------------------------+------------+
| 1 | New York                    | New York   |
| 2 | New York,Boston             | Boston     |
| 2 | New York,Washington         | Washington |
| 3 | New York,Washington,Boston  | Boston     |
| 3 | New York,Washington,Raleigh | Raleigh    |
+---+-----------------------------+------------+
```