SQL query

svenerik used Ask the Experts™
This may be a simple question, I don't know. I'm a real sql newbie...
Here's the problem:

I have a table for registering paths, and it looks somewhat like this:

pathID : NodeID
01 : Node01
01 : Node02
02 : Node01
02 : Node03
03 : Node03
03 : Node04
04 : Node04
04 : Node01

This data illustrates three nodes connected as a triangle like this:
Node1 - Node2 - Node3 - Node04 - Node1
similar to
Node1 -01- Node2 -02- Node3 -03- Node04 -04- Node1

What I would like is to get the pathID of a path providing the names of two nodes.
This way I also want to validate if two provided nodes are connected, ie isConnected(Node2, Node4) will return -1 and isConnected(Node1, Node2) will return 01.



Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

If there is only one path possible between two nodes (e.g. there cannot be the following:
01 : Node01
01 : Node02
02 : Node01
02 : Node02

Assuming you want to know if Node01 and Node02 are connected, use the following query:

select pathID
from   tblPath t1, tblPath t2
where  t1.NodeId = 'Node01'   and
       t2.pathId = t1.pathId  and
       t2.NodeId = 'Node02'

It uses a technique called "Self Join".
it's not the most efficient way, but it works.
In access I tried:

SELECT Node.pathID, Count(Node.pathID) AS CountOfpathID
WHERE Node.NodeID In ("node02","node04")))
GROUP BY Node.pathID
HAVING Count(Node.pathID)>1;

This will give the PathID when the given nodes are connected or nothing when there's no connection.
To have the True/False translation you'll need however some code to check the result of the query...



Just what I needed! It works great!


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial