I'm having a bit of difficulty wrapping my head around some queries that I need joined correctly to each other, in order to get the correct result.
Basically I got a database with a sewage network in it, the network has manholes with IDs and pipes with IDs. Each manhole and pipe has a unique ID. A pipe consists of an upstream and downstream manhole, each with separate IDs.
Example: Manhole 1 (ID: M1) Manhole 2 (ID: M2) makes a Pipe (ID: P1) consisting of upstream ID M1 and downstream ID M2.
The problem occurs when several pipes are in one manhole, which is defined as the upstream manhole for several of the pipes. Then it returns the correct manhole IDs but the wrong pipeID, as it is linked to the upstreamnode (where as I would like for it to check for both upstream and downstream in order to have the correct pipe ID)
It's a bit hard to explain, but made a paint drawing the tries to illustrate what I mean and the result I get.
Any hints to how I can correctly join this together would be much appreciated. I can't change the tablestructure, as it is linked to a program.