how determine the best path that satisfies a condition
Congratulations, you have a subset sum problem! It's an NP-complete problem. Arriving at the solution is going to call for brute force. i.e. Generate and test all possible subsets. WARNING: This may involve the sketchy Cartesian products your mother warned you about.

Depending on the size of your actual data domain, in SQL that is going to range from "computationally expensive but it works" at best to "who jammed the kernel to 100% and crashed the database server?" at worst.

As a computationally-intensive problem, it is more advisable to solve it in a dynamic programming language than in a SQL DBMS.