Hi Appache,
Change your query with below query.
Please refer below query.
SQL
Create table Siblings
(
Child varchar(20),
FatherId int,
MotherId int
)
INSERT INTO Siblings VALUES('a',2,2)
INSERT INTO Siblings VALUES('b',2,2)
INSERT INTO Siblings VALUES('c',4,3)
INSERT INTO Siblings VALUES('d',4,4)
INSERT INTO Siblings VALUES('e',5,5)
INSERT INTO Siblings VALUES('f',6,6)
INSERT INTO Siblings VALUES('g',7,7)
INSERT INTO Siblings(Child,MotherId) VALUES('h',2)
INSERT INTO Siblings(Child,MotherId) VALUES('i',2)
INSERT INTO Siblings(Child,FatherId) VALUES('j',2)
INSERT INTO Siblings(Child,FatherId) VALUES('k',2)
SELECT DISTINCT child,FatherId,MotherId FROM Siblings
WHERE FatherId IN(SELECT FatherId FROM Siblings WHERE FatherId <> 0 GROUP BY FatherId HAVING(COUNT(*)>1))
OR MotherId IN(SELECT MotherId FROM Siblings WHERE MotherId <> 0 GROUP BY MotherId HAVING(COUNT(*)>1))
Output
| child |
FatherId |
MotherId |
| a |
2 |
2 |
| b |
2 |
2 |
| c |
4 |
3 |
| d |
4 |
4 |
| h |
NULL |
2 |
| i |
NULL |
2 |
| j |
2 |
NULL |
| k |
2 |
NULL |
