Sfrgttk

(Oracle SQL)

I have been trying to figure out this problem on and off for a couple of days now, and it is driving me nuts. I don't have much experience with joins, or SQL in general, but I have gotten really close to solving this.

Two tables, Person and Parent.

Person has columns Person (primary key), Name, and Gender.

Parent has foreign key reference columns Person and Parent.

I didn't make the column names, it is just a part of the assignment.

This is the closest I have gotten:

SELECT Grandchild.Name AS Grandchild, Mother.Name AS Mother, Grandmother.Name AS Grandmother, Father.Name AS Father, Grandfather.Name AS Grandfather

FROM Parent Grandchild_

LEFT JOIN Person Grandchild ON (Grandchild.Person = Grandchild_.Person)

LEFT JOIN Person Mother ON (Grandchild_.Parent = Mother.Person AND Mother.Gender = 'F')

LEFT JOIN Person Father ON (Grandchild_.Parent = Father.Person AND Father.Gender = 'M')

LEFT JOIN Parent Mother_ ON (Mother_.Person = Mother.Person)

LEFT JOIN Parent Father_ ON (Father_.Person = Father.Person)

LEFT JOIN Person Grandmother ON (Grandmother.Person = Mother_.Parent AND Grandmother.Gender = 'F')

LEFT JOIN Person Grandfather ON (Grandfather.Person = Father_.Parent AND Grandfather.Gender = 'M')

WHERE COALESCE (Grandfather.Person, Grandmother.Person) IS NOT NULL

This has returned everything correctly, EXCEPT it doesn't combine the two "halves" of the mom's side and dad's side. There are two rows for each grand kid. Example output:

GRANDCHILD      MOTHER        GRANDMOTHER       FATHER      GRANDFATHER

Mark            Marina        Linda             -           -

Kelvin          Marina        Linda             -           -

Nathanial       Marina        Linda             -           -

Adrian          Marina        Linda             -           -

Catlin          Samantha      Linda             -           -

Dominique       Samantha      Linda             -           -

Mark            -             -                 Jackson     John

Kelvin          -             -                 Jackson     John

I don't know how to combine the two parents/grandparents (when available) while also keeping them linked to the child.

So the desired output would be the same, except in instances such as "Mark" and "Kelvin" where it should output:

GRANDCHILD      MOTHER        GRANDMOTHER       FATHER      GRANDFATHER

Mark            Marina        Linda             Jackson     John

Kelvin          Marina        Linda             Jackson     John

Does anyone know how to accomplish that and/or do this in a much simpler way? I feel like I am using so many joins when I don't need that many, but who knows.