Help with tricky query.

[Morrgan]

Or at least I find it tricky.

Assume we have three tables A, B and C. Table A contains a path and the distance for traveling that path:

A (PathId, NodeId, Dist (from previous node))
1, 1, 0
1, 2, 10
1, 3, 5

Table B contains observed data on path level:

B (ObsId, PathId)
1, 1
2, 1

Table C contains observed data on node level (~12M rows):

C (ObsId, NodeId, Time (from previous node))
1, 1, 0
1, 3, 10
2, 1, 0
2, 3, 1

What should be observed here is that only nodes where the traveler stopped are observed i.e. the time for node 3 here is the time for traveling from node 1.

What I would like to do is to filter out absurd observations in table B using the travel speed as condition. Something like:

SELECT B.ObsId
FROM JOIN...
WHERE DIST / C.Time < SPEED_LIMIT

The problem is to find out DIST. For me being a tired (at the moment)and rather inexperienced db programmer this seems a little bit tricky to solve but hopefully you guys and girls out there disagree.

 

[SkipVought]

Hi,

May I ask the business case for this exersize?

Skip,

_[red][/red]

 

[Morrgan]

Hi,

You may. Sorry for beeing so illusive. I am working in the transportation business. The purpose is to diagnose and repair a database containing information collected from our veichles.

 

[Morrgan]

A query like the one below will give me the correct result. However, needless to say it will be terribly slow.

SELECT SUM(Dist) FROM A WHERE
A.NodeId >
(SELECT MAX(CInner.NodeId) From C AS CInner WHERE CInner.ObsId = COuter.ObsId AND CInner.NodeId < COuter.NodeId) AND
A.NodeId <= COuter.NodeId

Any suggestions on a more efficient solution or do you think it is safe to say that this cannot be done in a reasonable amount of time? (Given the current design of the db and the information stored within.)