# 17.05 - Example: Multiple Seed and Recursive Statements - Advanced SQL Engine - Teradata Database

## Teradata Vantage™ - SQL Data Manipulation Language

Product
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
vnq1596660420420.ditamap
dita:ditavalPath
hoy1596145193032.ditaval

This example shows a recursive query that uses multiple seed and recursive statements.

Consider these two tables:

```    CREATE TABLE planes (
depart  VARCHAR(40),
arrive  VARCHAR(40),
carrier VARCHAR(40),
cost    DECIMAL(5,0));

CREATE TABLE trains (
depart VARCHAR(40),
arrive VARCHAR(40),
cost   DECIMAL(5,0));```

The data in the planes table is as follows:

Depart Arrive Carrier Cost
Paris New York AA 199
Paris London AA 99
London New York AA 199
New York Mexico City UA 99
Mexico City New York UA 99
Paris Mexico City AF 299
New York London AA 199
New York Tokyo JAL 999
Mexico City Tokyo JAL 999
Tokyo New York JAL 999

The data in the trains table is as follows:

Depart Arrive Cost
Paris London 99
London Paris 99
Paris Milan 199
London Milan 199
Milan Paris 199
Milan Rome 49
Rome Florence 49

The following query uses two seed statements and two recursive statements to return all cities reachable from Paris by train or plane.

```     WITH RECURSIVE temp_table (depart, arrive, carrier, depth) AS (
SELECT p_root.depart, p_root.arrive, p_root.carrier, 0 AS depth
FROM planes p_root
WHERE p_root.depart = 'Paris'
UNION ALL
SELECT t_root.depart, t_root.arrive, 'EuroRail', 0 AS depth
FROM trains t_root
WHERE t_root.depart = 'Paris'
UNION ALL
SELECT direct.depart, indirect.arrive, indirect.carrier, direct.depth+1
FROM temp_table AS direct, planes AS indirect
WHERE direct.arrive = indirect.depart
AND   indirect.arrive <> 'Paris'
AND   direct.depth <= 4
UNION ALL
SELECT direct.depart, indirect.arrive, 'EuroRail', direct.depth+1
FROM temp_table AS direct, trains AS indirect
WHERE direct.arrive = indirect.depart
AND   indirect.arrive <> 'Paris'
AND   direct.depth <= 4)
)
SELECT DISTINCT arrive (TITLE 'Destinations Reachable From Paris')
FROM temp_table;```

The result set for this recursive query is as follows:

```     Destinations Reachable From Paris
----------------------------------------
Florence
London
Mexico City
Milan
New York
Rome
Tokyo```