A query in SQL server very hard

Question

0.00/5 (No votes)

See more:

Hi all!
I have a problem in SQL Server ~~must everyone help~~.
My database has the form (ProID, SubProid, value)
example:

A---B---0.2
B---C---0.1
C---D---0.5
A---D---0.6
B---D---0.3

a given SubproId can have multiple paths to reach from a given proID.
How do I calculate the sum weight of every the path from the 2 points given
example(sum of every weight between A--D=0.6+0.8+0.5=1.9)?

Posted 19-Oct-12 22:56pm

binhminhtrencat

Updated 19-Oct-12 23:06pm

Zoltán Zörgő

v2

Add a Solution

Comments

Zoltán Zörgő 20-Oct-12 5:15am

Do you really need this in t-sql? Why not on higher level of the application?
Please give a more complex example.

Sandeep Mewara 20-Oct-12 5:46am

What have you tried so far? Where are you stuck?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jschell · Answer 1 · 2012-10-20T05:35:00

You should research the following

shortest path problem/algorithm

The presentation of the problem suggests to me it is homework. It isn't simple to solve and requires a non-trivial understanding of algorithms and SQL to solve it on ones own (I certainly couldn't solve it without researching the algorithm side.)

**Zoltán Zörgő** · Answer 2 · 2012-10-20T06:31:00

I don't see this as "shortest path" problem. More something that we could call "all paths" - but I am not sure. Ok, T-SQL is not the language for graph algorithms, but not impossible to handle either.
This is a good article about some general graph-related problem solutions in t-sql: http://hansolav.net/sql/graphs.html[^]. Here is an article about the "all paths" problem: http://www.technical-recipes.com/2011/a-recursive-algorithm-to-find-all-paths-between-two-given-nodes/[^]. This is not t-sql, but you can understand the concept.
You could use these approaches as samples and starting point. As you can see, it is not the simplest problem. I am pretty sure you will need to use fancy things like temporary tables and cursors.

A query in SQL server very hard

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0