filer pairs where non-candidate is multiple types

filer_pairs_where_other_is_multiple_types.sql

SELECT 
        @link_type as link_group, 
        cand_filer_id,
        other_filer_id,
        COUNT(DISTINCT other_filer_type) as filer_type_count
FROM (
        SELECT 
                links."FILER_ID_A" AS cand_filer_id,
                links."FILER_ID_B" AS other_filer_id,
                "LINK_TYPE" as link_type,
                others."FILER_TYPE" AS other_filer_type,
                links.id
        FROM "FILER_LINKS_CD" links
        JOIN (
                SELECT DISTINCT "FILER_ID"
                FROM "FILER_TO_FILER_TYPE_CD"
                WHERE "FILER_TYPE" = 8
        ) cands
        ON cands."FILER_ID" = links."FILER_ID_A"
        JOIN "FILER_TO_FILER_TYPE_CD" others
        ON links."FILER_ID_B" = others."FILER_ID"
        -- union with all link records where FILER B is a candidate
        UNION ALL
        SELECT 
                links."FILER_ID_B" AS cand_filer_id,
                links."FILER_ID_A" AS other_filer_id,
                "LINK_TYPE" as link_type,
                others."FILER_TYPE" AS other_filer_type,
                links.id
        FROM "FILER_LINKS_CD" links
        JOIN (
                SELECT DISTINCT "FILER_ID"
                FROM "FILER_TO_FILER_TYPE_CD"
                WHERE "FILER_TYPE" = 8
        ) cands
        ON cands."FILER_ID" = links."FILER_ID_B"
        JOIN "FILER_TO_FILER_TYPE_CD" others
        ON links."FILER_ID_A" = others."FILER_ID"
) as foo
GROUP BY 1, 2, 3
HAVING COUNT(DISTINCT other_filer_type) > 1;

link_group  cand_filer_id  other_filer_id  other_filer_type_count  
----------  -------------  --------------  ----------------------  
12011       1005184        1277980         2                       
12011       1293665        1293663         2