Tracing Upstream

[Guest]

Hello,

I am trying to trace upstream of a point to find potential features that may
be connected at some finite (yet varying) location.

For instance, my point's ID is ABC123. The pipe that connects to it has a
downstream_ID of ABC123 and an upstream_ID of ABC345. It connects to another
pipe with DS_ID = ABC345 and US_ID = ABC567. Pipe 3 then connects to the
feature that I am looking for that has a UNITTYPE = CHSWL and DS_ID = ABC567.

How can I develop a query that will trace from my downstream point to the
feature I'm looking for? Note that the feature may not always be 3 pipes
upstream, or there may be no feature at all. I have somewhat of an idea using
IF statements, but how can I iteravely run IF statements in a query? Thanks
for any help you may be able to provide. Ruben.

 

[Guest]

Try this --
SELECT Jose_Ruben.UNITTYPE, Jose_Ruben.DS_ID, Jose_Ruben.US_ID,
Jose_Ruben_1.UNITTYPE, Jose_Ruben_1.US_ID, Jose_Ruben_2.UNITTYPE,
Jose_Ruben_2.US_ID, Jose_Ruben_3.UNITTYPE
FROM ((Jose_Ruben LEFT JOIN Jose_Ruben AS Jose_Ruben_1 ON Jose_Ruben.US_ID =
Jose_Ruben_1.DS_ID) LEFT JOIN Jose_Ruben AS Jose_Ruben_2 ON
Jose_Ruben_1.US_ID = Jose_Ruben_2.DS_ID) LEFT JOIN Jose_Ruben AS Jose_Ruben_3
ON Jose_Ruben_2.US_ID = Jose_Ruben_3.DS_ID
WHERE (((Jose_Ruben.DS_ID)="ABC123"));