Help with tricky query?

[MorrganMail]

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 (for the moment)
and rather inexperienced db programmer this seems a little bit tricky
to solve but hopefully you guys and girls out there disagree.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Just a shot in the dark....

PARAMETERS speed_limit INTEGER;
SELECT B.ObsID
FROM (A INNER JOIN B ON A.PathID = B.PathID) INNER JOIN C ON A.NodeID =
C.NodeID AND B.ObsID = C.ObsID
WHERE C.[Time] <0
AND A.Dist / C.[Time] < speed_limit
ORDER BY B.ObsID DESC

Make sure you have indexes on NodeID and ObsID columns in the C table
(for speed) [in the other tables, as well, if they have a large number
of rows].
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUBH8IechKqOuFEgEQLNEgCfZT8uYfpHPKkoBkqpPL2MVF TTOfIAn1xF
aXwnLbx4i0IoTlkhAx2HV0i7
=43md
-----END PGP SIGNATURE-----
Mo*********@gmail.com wrote:

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 (for the moment)
and rather inexperienced db programmer this seems a little bit tricky
to solve but hopefully you guys and girls out there disagree.