SELECT
    t.id AS individual_id,
    t.name,
    t.role,
    t.affiliation AS current_affiliation,
    p.affiliation AS historical_affiliation
FROM surveillance_data.current_targets AS t
INNER JOIN napoleon_data.historical_people AS p
    ON
        t.name = p.name
        OR t.affiliation != 'N/A'
WHERE
    EXISTS (
        SELECT 1 FROM (SELECT
            a1.group_name_1,
            a2.group_name_2
        FROM surveillance_data.group_affinities AS a1,
            surveillance_data.group_affinities AS a2
        WHERE a1.group_name_2 = a2.group_name_1
        UNION
        SELECT
            group_name_1,
            group_name_2
        FROM surveillance_data.group_affinities) AS test
        WHERE
            (group_name_1 = p.affiliation AND t.affiliation = group_name_2)
            OR (group_name_1 = t.affiliation AND group_name_2 = p.affiliation)
    )
    OR t.affiliation = p.affiliation