SQL: display children and brothers/sisters

Question

I have a database that basically stores a family tree made of several families.

I have two tables:

1. "persons": id, name, lname
2. "relationships": parent, child

Displaying all parents is ok, I did this:

SELECT DISTINCT p.fname, p.lname
FROM persons p
INNER JOIN relationships ON p.id = relationships.p
INNER JOIN persons c ON c.id = relationships.c;

But how could I display comprehensively each child and its respective brothers/sisters?

I tried a select within a select among many things but it's not working:

SELECT DISTINCT 
    c1.name, 
    c1.lname,
    (
    SELECT 
        c2.firstname, 
        c2.lastname 
    FROM 
        persons p2 
    INNER JOIN relations  ON p2.id = relations.parent 
    INNER JOIN persons c2 ON c2.id = relations.child
    WHERE 
        p.id = p2.id
    ) 
FROM 
    persons p
INNER JOIN relations  ON p.id  = relations.parent 
INNER JOIN persons c1 ON c1.id = relations.child;

I think I'm on the wrong path.

Thanks for your help.

Answer 1

If I understand correctly, this is just a self join:

select r.child, r2.child as sibling
from relationships r join
     relationships r2
     on r.parent = r2.parent and
        r.child <> r2.child
order by r.child, r2.child;

For the names from the other table you need more joins:

select p.*, p2.*
from relationships r join
     relationships r2
     on r.parent = r2.parent and
        r.child <> r2.child join
     persons p
     on p.id = r.child join
     persons p2
     on p2.id = r2.child
order by r.child, r2.child;