CODEWITHSUNDEEP
sql
YooTeeEff
YooTeeEff

Reputation: 1

SQL Join Entries That Have Indirect Relationship

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 :)

Upvotes: 0

Views: 365

Answers (3)

Mohsen Karimi
Mohsen Karimi

Reputation: 1

There are some conditions:

1 - If you have an indirect relationship you should use one or more relational tables in your database that have a direct relation. This is not recommended as it is demanding and time-consuming for your SQL server, like this:

select * 
from table_name1 as a  
inner join table_name2 as b 
on a.col1 = b.col1
inner join relational_table as c 
on b.col1 = c.col1

Be careful that a.col1, b.col1 and c.col1 are not the same so you may get the wrong answer.

2 - If you have a direct relation between your table you should just join them based on their keys:

select *
from table_name1 as a 
inner join table_name2 as b 
on a.col1 = b.col1

3 - If there is not any relationship you should use a comma in front of the 'from' command or use 'cross join', but it will show you cartesian products, like this:

select * 
from table_name1, table_name2 
-----or 
select * 
from table_name1 cross join table_name2

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

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;

Upvotes: 0

Himanshu
Himanshu

Reputation: 3970

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

Upvotes: 2

Related Questions