Having difficulty understanding many-to-many recursive

Question

CREATE TABLE PERSON
(
    persID      INT IDENTITY(1,1)   PRIMARY KEY,
    persFName   VARCHAR(30) NOT NULL,
    persLName   VARCHAR(30) NOT NULL, 
    persDOB     DATE,
    motherID    INT FOREIGN KEY REFERENCES person(persID),
    fatherID    INT FOREIGN KEY REFERENCES person(persID),
    persDOD     DATE,
    persGender  CHAR(1),
)

CREATE TABLE COUPLE
(
    coupleID    INT     IDENTITY(1,1)   PRIMARY KEY,
    alphaSpouse INT     NOT NULL    FOREIGN KEY REFERENCES person(persID),
    omegaSpouse INT     NOT NULL    FOREIGN KEY REFERENCES person(persID),
    coupleStart DATE    NOT NULL,
    coupleEnd   DATE,
)

For the past few hours I've been trying to figure out how to output each father, mother and all their children. Trying to get an output like this

I know how to display one group individually, like below

SELECT *
FROM person
WHERE persID IN (select fatherID from person
                    WHERE fatherID IS NOT NULL)

but I cannot for the life of me figure out how to display them together (while relating to each other). Do I use cross join? union all?

Answer 1

This is just a join, several times. It has nothing to do with the couple table:

select (f.persFname + ' ' + f.persLname) as father,
       (m.persFName + ' ' + m.persLname) as mother,
       (c.persFName + ' ' + c.persLname) as child
from person c join
     person m  -- mother
     on c.motherid = m.persid join
     person f  -- father
     on c.fatherid = f.persid;