CODEWITHSUNDEEP
sqlgraphsqlite
Buddy
Buddy

Reputation: 1836

Select edge with both vertices from SQL db storing a graph structure

I have two tables to store graph data.

Vertex table:

+----+---+---+
| id | x | y |
+----+---+---+

And the edge table (many-to-many relationship):

+---------+-------+
| from_id | to_id |
+---------+-------+

I want to select all edges with the corresponding vertices like this:

+----+----+----+----+
| x1 | y1 | x2 | y2 |
+----+----+----+----+

where x1 and y1 are the coordinates of the source vertex and x2 and y2 are the coordinates of the destination vertex.

What is the corresponding SQL query?

Upvotes: 0

Views: 252

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

SELECT v_from.x x1, 
       v_from.y y1,
       v_to.x   x2,
       v_to.y   y2
FROM edge AS e
JOIN vertex AS v_from
  ON e.from_id = v_from.id
JOIN vertex AS v_to
  ON e.to_id = v_to.id

Upvotes: 1

Related Questions