title: Mysql Edge List, combining subquery result
body: <h2>What I have:</h2>
  
  <p>two tables.</p>
  
  <p>T1:<br>
  | Node-id | Position(X,Y) |<br>
  ----------------------------</p>
  
  <p>and</p>
  
  <p>T2:<br>
  | Edge-id | Node-Id1 | Node-Id2 |<br>
  ---------------------------------------------</p>
  
  <h2>What I need:</h2>
  
  <p>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.</p>
  
  <p>It seems however I have stumbled on to a slightly non trivial query, or at least non trivial for me. </p>
  
  <p>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...</p>
  
  <p>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.</p>
  
  <h2>What I tried...</h2>
  
  <p>Not that it is of particularly much use but here is the query which has gotten me closest.</p>
  
  <pre>
  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
  </pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Cheers</p>