SQL assistance requested 1

[VickyC]

I am asking for help with a query that involves 2 tables:

[tt]tblPaths tblCost
PathID Node1 Node2 Node3 ... Node12 Node1st Node2nd Cost
1 24 19 38 ... 18 1 2 10
2 33 5 93 ... 87 1 3 8
3 80 12 62 ... 71 1 4 27
... 1 5 3
100,000+ ....
100 98 15
100 99 62[/tt]


In tblPath, there are many records, each showing 12 numbered nodes that can be any value from 1 to 100.

In tblCost, we see the 'Cost' of moving from one node to the next. For example, moving from node1 to node5 has a 'cost' of 3 (units are arbitrary).

Here's what I need the query to do. For each PathID, I need the TOTAL Cost. For example, when PathID = 3, the Total Cost = the cost of moving from (80 to 12) + the cost of moving from (12 to 62) + ... etc to 71.

I'm having a rough time getting this to work properly. Because I need to do this process for a number of very large tables, I'm really looking for efficiency.

Many thanks
Vicky

 

[BigRed1212]

What I would do is this:

Create a normalizing query to put your path data into a proper structure:

SELECT pathid, node1 as node1st, node2 as node2nd
from tblPaths
union
SELECT pathid, node2 as node1st, node3 as node2nd
from tblPaths
union
SELECT pathid, node3 as node1st, node4 as node2nd
from tblPaths

etc etc

SELECT pathid, node11 as node1st, node12 as node2nd
from tblPaths

That should give you results like:

pathid	node1st	node2nd
1	1	2
2	1	8
3	80	12
1	2	16
1	16	42
2	8	15
2	15	80
3	12	62
3	62	71
1	42	56
2	80	99
3	56	99
1	56	99

Then against that proper normalized structure (call it newPath or something), your cost for each path is a simple:

SELECT pathid, sum(cost) as cost
from newPath inner join cost on newPath.node1st=cost.node1st and newPath.node2nd= cost.node2nd
group by pathid
order by pathid

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

Thanks to BigRed1212 for some nice code and to PHV for the useful reference.

I knew that my tblPaths wasn't normalized, but I thought this would be OK because a) every record had exactly 12 node values, so there would be no 'gaps', and b) there was no chance of update anomalies, etc. But, I guess I missed the fact that normalization also makes it much easier to write the kind of SQL that was provided in this case.

Thanks, Vicky