berkyl berkyl - 1 year ago 77
PHP Question

Reciprocal relationship between persons

I'm developing a small app to manage persons and their relationships.

I created a model for relationships on the same "level" like marriage and siblings. To represent parents and children I use self referencing fields.

SQL Fiddle


The idea behind this is, to have reciprocal relationships between persons without violating any NF's and to avoid duplicates.

My problem is, that the query to get all persons which are related to a person is complex since with a normal join the corresponding person would be in the resultset too.

Is there a way to query all persons related to person A without having person A in the resultset?

Is there a better way to represent reciprocal relations?

Answer Source

Just use the where clause to filter out person A from the resultset. Self join the persons_relationships table on itself, filtering for person A (with the id of x) in one instance, and excluding the same person from the other one:

select pr2.person_id from
persons_relationships pr1
inner join persons_relationships pr2 on pr1.relationship_id=pr2.relationship_id
where pr1.person_id=x and pr2.person_id<>x;