0

What I have:

two tables.

T1:
| Node-id | Position(X,Y) |
----------------------------

and

T2:
| Edge-id | Node-Id1 | Node-Id2 |
---------------------------------------------

What I need:

I would like to essentially iterate over the edge id's, retrieve the Node-id, then access the Position for every node pair. This will in turn allow me to draw an edge in my application, between all connected nodes in a graph.

It seems however I have stumbled on to a slightly non trivial query, or at least non trivial for me.

I believe I need to use a subquery, as I want to take all the nodes from my edge Id, forward them to the node table and retrieve the position. However MySql complains when I try to return multiple columns...

Is there anyway to treat Node-Id1 and Node-Id2 as a single large column and return that from the subquery? Then I would just need to remember that Xpos2 and Ypos2 start half way down the row count.

What I tried...

Not that it is of particularly much use but here is the query which has gotten me closest.

SELECT X( `LOCATION` ) AS Xpos1, Y( `LOCATION` ) AS Ypos1
FROM  `nodes` ,  `edges` 
WHERE  `NODE ID` 
IN (
SELECT  `NODE ID1` as `NODE ID`
FROM  `edges` 
WHERE  `EDGE ID` =0
)
GROUP BY  `NODE ID` 
LIMIT 0 , 30

The above query however is missing a very important part. Which is the NODE ID2 bit which would ideally send its results to an Xpos2 and Ypos2.

On the off chance, there is a better table design, I am all ears and don't have a problem changing it. I cannot however change to a graph database.

Cheers

mwjohnson
  • 661
  • 14
  • 26

1 Answers1

0

Here is the elusive line I was looking for:

SELECT X( n1.LOCATION ) AS Xpos1, Y( n1.LOCATION ) AS Ypos1, X( n2.LOCATION ) AS Xpos2, Y( n2.LOCATION ) AS Ypos2
FROM edges
INNER JOIN nodes n1 ON ( n1.`NODE ID` = edges.`NODE ID1` ) 
INNER JOIN nodes n2 ON ( n2.`NODE ID` = edges.`NODE ID2` ) 
LIMIT 0 , 30

This answer and the related question provided the insight I needed.

Community
  • 1
  • 1
mwjohnson
  • 661
  • 14
  • 26