sp_AutoJoinNetworkDevices

BEGIN

    /* add network devices into groups from last executed searches */

    INSERT IGNORE

    INTO SubGroupsNetworkDevices(GroupID, DeviceID) (

        SELECT MasterGroups.GroupID,

            Searches.NetworkDeviceID

        FROM MasterGroups

        JOIN Searches ON Searches.SearchID = MasterGroups.NetworkJoin

        WHERE NetworkJoin > 0);

    /* remove network devices from groups where the search options were excusionary */

    DELETE SubGroupsNetworkDevices

    FROM SubGroupsNetworkDevices

    JOIN MasterGroups USING (GroupID)

    WHERE (MasterGroups.NetworkJoinOptions = 1

        AND DeviceID NOT IN (

            SELECT NetworkDeviceID

            FROM Searches

            WHERE SearchID = MasterGroups.NetworkJoin))

        OR DeviceID NOT IN (SELECT DeviceID FROM `networkdevices`);

    /* create temp table of (groupid, deviceid) where group is not a master and device belongs to another group that is a master */

    CREATE TEMPORARY TABLE IF NOT EXISTS GNDevice

    SELECT *

    FROM SubGroupsNetworkDevices

    WHERE GroupID IN (

            SELECT GroupID

            FROM MasterGroups

            WHERE `master` = 0)

        AND DeviceID IN (

            SELECT DeviceID

            FROM SubGroupsNetworkDevices

            JOIN MasterGroups USING (groupID)

            WHERE MasterGroups.NetworkJoin > 0

                AND MasterGroups.Master = 1);

    /* use above temp table to remove devices from non-master groups if they're already in other master groups */

    DELETE SubGroupsNetworkDevices

    FROM SubGroupsNetworkDevices

    JOIN GNdevice USING (DeviceID, GroupID);

    DROP TEMPORARY TABLE GNDevice;  /* goodbye temp table */

END