Agfdhyk

My problem statement is: I need to find places I can visit from Origin 'A' and their respective costs.

This is my table Train(Origin, Destination, LeastCost)

 +--------+-------------+------+

 | Origin | Destination | cost |

 +--------+-------------+------+

 | A      | B           |    1 |

 | A      | C           |    4 |

 | B      | C           |    2 |

 | A      | D           |    4 | 

 +--------+-------------+------+

I have tried a query:

with recursive Final(Origin, Destination, LeastCost) As(

-> (Select * from Train)

-> UNION

-> (Select T.Origin, F.Destination, F.LeastCost

-> from Train T, Final F

-> where T.Destination = F.Origin))

-> select * from Final ;

This gives me:

+--------+-------------+-----------+

| Origin | Destination | LeastCost |

+--------+-------------+-----------+

| A      | B           |         1 |

| A      | C           |         4 |

| B      | C           |         2 |

| A      | D           |         4 |

| A      | C           |         2 |

+--------+-------------+-----------+

The result I am looking for is

Origin | Destination | Price |

 A            C          3

As A-->B = 1, B-->C=2 , So A-->C=1+2=3 in the last row.

How do I achieve this? I tried using SUM(LeastCost) inside the recursive query but MySQl doesn't allow aggregations in there.

Add the two costs from the T and F aliases together in the recursive query.
And then put additional logic in the final query to group the results:

with recursive Final(Origin, Destination, LeastCost) As(

    (Select * from Train)

    UNION

    (Select T.Origin, F.Destination, T.cost + F.LeastCost

     from Train T, Final F

     where T.Destination = F.Origin)

)

select   Origin, Destination, min(LeastCost)

from     Final

group by Origin, Destination

Through the recursive mechanism T.cost + F.LeastCost will make the cost sum up as you travel from one node through the tree to another.