SQL Count people who have all 4 grandparents

user_7924577 0
Viewed 59

So I have these following tables:

1) ADDRESS PERSON_ID | START_DATE | END_DATE | STREET | HOUSE | ROOM | ZIPCODE | CITY

2) IDENTIFIER NR | PERSON_ID | ISSUE_DATE | VALID_UNTIL

3) MARRIAGE WIFE_ID | HUSBAND_ID | START_DATE | END_DATE

4) PERSON ID | FIRST_NAME | LAST_NAME | BIRTHDATE | MOTHER_ID | FATHER_ID

And my task is to calculate the number of people who have all 4 grandparents recorded in the database. So if I got it right, I have to find all the people in the table where both MOTHER_ID and FATHER_ID are not null, whose MOTHER_ID and FATHER_ID columns are also not null.

What I have for now is:

SELECT COUNT(*)
FROM PERSON
WHERE MOTHER_ID IS NOT NULL AND FATHER_ID IS NOT NULL

which returns the number of people who have both parents recorded in the database and I'm stuck at this point.

select oracle sql
2 Answers

I'd join the table on itself twice, once for the father and once for the mother, and then check their parents:

SELECT COUNT(*)
FROM   person p
JOIN   person f ON p.father_id = f.id AND f.father_id IS NOT NULL AND f.mother_id IS NOT NULL
JOIN   person m ON p.mother_id = m.id AND m.father_id IS NOT NULL AND m.mother_id IS NOT NULL
user_24227760

You should join two instance of Person table together and then select rows with four mother|father_1|2 is not null. the sql is something like this.


SELECT p1.father_id AS father_id_1, p2.father_id AS father_id_2,p1.mother_id AS mother_id_1,p2.mother_id AS mother_id_2, 
FROM Person p1
JOIN Person p2 ON p1.FATHER_ID = p2.ID and p1.MOTHER_ID = p2.ID
user_41829060
Related