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.
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?
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;