sp_AutoJoinContacts

BEGIN

    /* add contacts into groups from last executed searches */

    INSERT IGNORE

    INTO SubGroupsContacts(GroupID, ContactID) (

        SELECT MasterGroups.GroupID,

            Searches.ContactID

        FROM MasterGroups

        JOIN Searches ON Searches.SearchID = MasterGroups.ContactJoin

        WHERE ContactJoin > 0);

    /* remove contacts from groups where the search options were excusionary */

    DELETE SubGroupsContacts

    FROM SubGroupsContacts

    JOIN MasterGroups USING (GroupID)

    WHERE (MasterGroups.ContactJoinOptions = 1

        AND ContactID NOT IN (

            SELECT ContactID

            FROM Searches

            WHERE SearchID = MasterGroups.ContactJoin))

        OR ContactID NOT IN (SELECT ContactID FROM `contacts`);

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

    CREATE TEMPORARY TABLE IF NOT EXISTS GContacts

    SELECT *

    FROM SubGroupsContacts

    WHERE GroupID IN (

            SELECT GroupID

            FROM MasterGroups

            WHERE `master` = 0)

        AND ContactID IN (

            SELECT ContactID

            FROM SubGroupsContacts

            JOIN MasterGroups USING (groupID)

            WHERE MasterGroups.ContactJoin > 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 SubGroupsContacts

    FROM SubGroupsContacts

    JOIN GContacts USING (ContactID, GroupID);

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

END