CODEWITHSUNDEEP
sqlsqlite
CodyBugstein
CodyBugstein

Reputation: 23322

How do I find sql records with certain identical attributes?

I have a table called parents: 

________________________________
|childID | motherID | fatherID |
--------------------------------
|   2    |     1    |    100   |
|   3    |     2    |    101   |
|   4    |     2    |    101   |
...

I need to get a table returned with the childIDs of all the children who have the same parents.

I tried 

SELECT childID
FROM parents
GROUP BY motherID, fatherID

but that also is giving me children who do not have a sibling.

Upvotes: 0

Views: 54

Answers (1)

Z .
Z .

Reputation: 12837

SELECT
    ChildId 
FROM
    parents p
    JOIN
    (
        SELECT
            motherID,
            fatherID,
            COUNT(*)
        FROM
            parents
        GROUP BY
            motherID,
            fatherID
        HAVING
            COUNT(*) > 1
    ) motherFatherCount
    ON p.MotherId = motherFatherCount.MotherID AND
       p.FatherId = motherFatherCount.FatherId

Upvotes: 2

Related Questions