I created a table in DB2:
CREATE TABLE mytable
(
loan_id integer,
client_id integer
);
Inserted values into the table:
INSERT INTO mytable(
loan_id, client_id)
VALUES (1, '2');
Finally the table contains the following data:
loan_id client_id
1 2
1 4
4 2
2 3
I want to run a SQL query which would enumerate (print) all the unique possible paths. An example output is given below:
1 -> 2
1 -> 4
4 -> 2
2 -> 3
1 -> 2 -> 3
4 -> 1 -> 2
4 -> 2 -> 3
4 -> 1 -> 2 -> 3
3 -> 2 -> 4 -> 1
I looked into the following answer and tried the following code, which gives error:
WITH links AS
( SELECT
loan_id,
client_id as c1,
client_id as c2, 0 as distance
FROM
mytable
-- recursion
UNION ALL
SELECT
t.loan_id,
l.c1 as c1,
tt.client_id as c2,
distance+ as distance
FROM
links l INNER JOIN
myTable t ON l.c2 = t.client_id
AND l.loan_id != t.loan_id INNER JOIN
myTable tt ON t.loan_id = tt.loan_id
AND t.client_id != tt.client_id
)
SELECT * FROM mytable t
WHERE EXISTS
(SELECT * FROM links
WHERE c2 = t.client_id and c1 = 3);
Thanks for help.