sp_DoSubGroupChildren

BEGIN

-- temp table

    CREATE TEMPORARY TABLE tgroup (KEY (`pri`))

    SELECT m2.groupid AS pri, m.groupid FROM mastergroups m, mastergroups m2 WHERE m.groupid=m2.groupid OR FIND_IN_SET(m.groupid,m2.children);

    -- temp table for computer data 

    CREATE TEMPORARY TABLE tcomputer (KEY (`pri`,`ComputerId`))

    SELECT DISTINCT tgroup.pri, s.computerid FROM tgroup JOIN subgroups s USING(groupid);

    -- computer data to remove.

    DELETE swc FROM subgroupwchildren swc LEFT JOIN tcomputer AS v ON swc.groupid=v.pri AND swc.computerid=v.computerid WHERE v.pri IS NULL AND v.computerid IS NULL;

    -- computer data to add

    INSERT INTO subgroupwchildren (GroupID, ComputerID) SELECT v.pri, v.computerid FROM subgroupwchildren swc 

    RIGHT JOIN tcomputer AS v ON swc.groupid= v.pri AND swc.computerid = v.computerid WHERE swc.groupid IS NULL AND swc.computerid IS NULL;

    -- remove temp table 

    DROP TEMPORARY TABLE tcomputer;

    -- temp table for contact data 

    CREATE TEMPORARY TABLE tcontact (KEY (`pri`,`contactId`))

    SELECT DISTINCT tgroup.pri, s.contactid FROM tgroup JOIN subgroupscontacts s USING(groupid);

    -- contacts data to remove

    DELETE swcc FROM subgroupwchildrencontacts swcc LEFT JOIN tcontact AS v ON swcc.groupid=v.pri AND swcc.contactid=v.contactid WHERE v.pri IS NULL AND v.contactid IS NULL;

    -- contacts data to add

    INSERT INTO subgroupwchildrencontacts (GroupId, ContactId) SELECT v.pri, v.contactid FROM subgroupwchildrencontacts swcc 

    RIGHT JOIN tcontact AS v ON swcc.groupid=v.pri AND swcc.contactid=v.contactid WHERE swcc.groupid IS NULL AND swcc.contactid IS NULL;

    -- remove temp table 

    DROP TEMPORARY TABLE tcontact;

    -- temp table for network device data

    CREATE TEMPORARY TABLE tnetwork  (KEY (`pri`,`deviceId`))

    SELECT DISTINCT tgroup.pri, s.deviceid FROM tgroup JOIN subgroupsnetworkdevices s USING(groupid);   

    -- network device data to remove

    DELETE swcnd FROM subgroupwchildrennetworkdevices swcnd LEFT JOIN tnetwork AS v ON swcnd.groupid=v.pri AND swcnd.deviceid=v.deviceid WHERE v.pri IS NULL AND v.deviceid IS NULL;

    -- network device data to add

    INSERT INTO subgroupwchildrennetworkdevices (GroupId, DeviceId) SELECT v.pri, v.deviceid FROM subgroupwchildrennetworkdevices swcnd 

    RIGHT JOIN tnetwork AS v ON swcnd.groupid=v.pri AND swcnd.deviceid=v.deviceid WHERE swcnd.groupid IS NULL AND swcnd.deviceid IS NULL;

    -- remove temp table 

    DROP TEMPORARY TABLE tnetwork;

    -- remove initial temp table 

    DROP TEMPORARY TABLE tgroup;

END