CODEWITHSUNDEEP
mysqlsql
hardfork
hardfork

Reputation: 3261

How to write a "shortest path" like command or at least a "find a path" command in MYSQL?

Imaging, there are social-media-data in a table like this:

u1 u2
0  3
1  2
1  4
2  3
3  1
3  4

It means:

Now I want to know: Is there a way/path from User 0 to User 2?

Yes. User 0 follows User 3. User 3 follows User 1. User 1 follows User 2.

But how can a solve this problem as a SQL-Command?

I want to know if there is such a path. Optional I want to know what the path is. And is it possible to get the shortest path somehow?

Upvotes: 1

Views: 97

Answers (1)

The Impaler
The Impaler

Reputation: 48810

The query below works in MySQL 8.x since it requires a "Recursive Common Table Expression" (Recursive CTE):

with n (initial, path, current) as (
  select u1, concat('', u1, '/', u2), u2  from my_table 
    where u1 = 0 -- initial node
  union all 
  select n.initial, concat(n.path, '/', m.u2), m.u2
    from my_table m
    join n on n.current = m.u1
)
select * from n 
  where current = 2 -- target node

However, if you are using MySQL 5.x, then you're out of luck (to the best of my knowledge).

Upvotes: 1

Related Questions