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. I don't think we are supposed to complete this using unions or anything, as we haven't gotten that far yet.