SQL Join Entries That Have Indirect Relationship-CodePudding

I am loooking for a way to join entries based on an indirect relationship, such as having edges that represents the elements that are linked (e.g. indirect graph).

For example, for the following tables:

Nodes Table

A Joe
B Alice
C Bob
D Jane

Edges Table

A B
B D
C B

The resultant table I am looking for should look like the follwing:

Joe Alice
Alice Jane
Bob Alice

Is using JOIN the approach I should take? How should the query look like in this example?

Thank you in advance :)

CodePudding user response：

Try this

     Select n.col2, n1.col2 from
     nodes n join edges e
     on n.col1=e.col1
     join
     nodes n1  on n1.col2=e.col2

CodePudding user response：

You want to select the edges and look up the node names from the nodes table. In order to do so join the nodes table twice, because there are two node IDs in the edges table. Give the two node rows different aliases, so you know which is which.

select
  n1.node_name as node_1,
  n2.node_name as node_2
from edges e
join nodes n1 on n1.node_id = e.node_1_id
join nodes n2 on n2.node_id = e.node_2_id
order by node_1, node_2;