joining f460s to filer_links

with_filer_id.sql

-- 1,471 uncaptured records
SELECT COUNT(DISTINCT cvr."FILER_ID")
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" cvr
JOIN f460_summary f460
ON cvr."FILING_ID" = f460.filing_id
AND cvr."AMEND_ID" = f460.amend_id
JOIN "FILER_XREF_CD" x
ON x."XREF_ID" = cvr."FILER_ID"
LEFT JOIN (
        SELECT "FILER_ID_A"::varchar as filer_id
        FROM "FILER_LINKS_CD"
        UNION 
        SELECT "FILER_ID_B"::varchar as filer_id
        FROM "FILER_LINKS_CD"
) links
ON links.filer_id = cvr."FILER_ID"
WHERE links.filer_id IS NULL;

with_xref_id.sql

-- 7 uncaptured records
SELECT COUNT(DISTINCT cvr."FILER_ID")
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" cvr
JOIN f460_summary f460
ON cvr."FILING_ID" = f460.filing_id
AND cvr."AMEND_ID" = f460.amend_id
JOIN "FILER_XREF_CD" x
ON x."XREF_ID" = cvr."FILER_ID"
LEFT JOIN (
        SELECT "FILER_ID_A" as filer_id
        FROM "FILER_LINKS_CD"
        UNION 
        SELECT "FILER_ID_B" as filer_id
        FROM "FILER_LINKS_CD"
) links
ON links.filer_id = x."FILER_ID"
WHERE links.filer_id IS NULL;