mysql join a table to itself from a bridging table-CodePudding

I have two tables that references each other via id. I want to connect a given name in the first table, to a relation with one or many persons using a second table. See example

Person

id	name	Adress
1	Jonas	Sturmwind Street 12 5431
2	Thomas	New Banksy Home 14 5432
3	Therese	Redcarpet Willow 1 6623
4	Nicko	Redcarpet Willow 1 6623
5	Sandra	Mcmurdo Station

related_from_id	related_to_id	Relation
1	2	"Sibling"
1	5	"Sibling"
3	4	"Parent"

Given a name, how can I make a mysql query that gives me any potential siblings name and adress.

For instance I query with param "Jonas" and expect the result set to be

Relation

Name	Related to	Adress
Jonas	"Thomas"	"New Banksy Home 14 5432"
	"Sandra"	"Mcmurdo Station"

CodePudding user response：

I would assume you want to walk the related table in any direction (left-right or right-left).

You can do:

select p.name, o.name as related_to, o.address
from person p
join related r on p.id in (r.related_from_id, r.related_to_id)
join person o on r.related_from_id = p.id and o.id = related_to_id
              or r.related_to_id = p.id and o.id = related_from_id
where p.name = 'Jonas'

CodePudding user response：

In order to have your result SELECT p.name AS NAME, p1.name AS relatedTo, p1.Address FROM Person p, Related r, Person p1 WHERE r.related_from_id = p.id AND r.related_to_id = p1.id AND p.name = 'Jonas' ;

Howover if the relation is in any direction, @The Impater's answer has you covered.