I need to find the most efficient way in MySQL to compare 2 different instances of a one to many relationship. Take this table
CREATE TABLE `Table` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`ParentID` int(11) NOT NULL,
`ChildID` int(11) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `pach` (`ParentID`,`ChildID`),
KEY `ParentID` (`ParentID`),
KEY `ChildID` (`ChildID`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
insert into `Table` (`ID`,`ParentID`,`ChildID`) values
(1,1,1),
(2,1,2),
(3,1,3),
(4,1,4),
(5,2,1),
(6,2,3),
(7,3,1),
(8,3,3),
(9,3,4),
(10,4,1),
(11,4,4),
(12,4,3);
ParentID 3 has an identical set of children to ParentID 4, and that's what i need my query to be able to identify - Given ParentID=4, return ParentID 3 because it has exaclty the same children.
So far the only thing i can come up with is a very ugly group_concat query (see below). What would be a better approach to solve this problem?
select distinct(b.ParentID)
from `Table` a, `Table` b where
(select group_concat(ChildID order by ParentID asc) from `Table` where ParentID=a.ParentID )
=
(select group_concat(ChildID order by ParentID asc) from `Table` where ParentID=b.ParentID )
and b.ParentID!=a.ParentID
and a.parentID=4;
+----------+
| ParentID |
+----------+
| 3 |
+----------+