Hacky SQL query to get a report re: Mrs./Miss name forms from ArchivesSpace

aspace_agent_overview_for_mrs_and_miss.sql

SET SESSION GROUP_CONCAT_max_len=20000;
# the length is too small by default to search for all variations in the name forms...  should consider another tactic for this, though.

SET @base_public_uri = 'https://archives.yale.edu/';

# specify database name, if needed.

/* Requested fields:
Agent Name	Display name	Authorized name	URI	Record type (resource or accession)	Repository	Source	Identifier/call number
*/
SELECT
    GROUP_CONCAT(DISTINCT CASE WHEN np.authorized THEN np.sort_name END) AS 'authorized name form',
    GROUP_CONCAT(DISTINCT CASE WHEN np.is_display_name THEN np.sort_name END) AS 'display name',
    GROUP_CONCAT(DISTINCT np.sort_name SEPARATOR ' | ') AS 'all_name_forms',
    GROUP_CONCAT(DISTINCT CASE WHEN np.authorized IS NULL AND np.is_display_name IS NULL THEN np.sort_name END SEPARATOR ' | ') AS 'other name forms',
    GROUP_CONCAT(DISTINCT nai.authority_id) AS 'authority id',
    GROUP_CONCAT(DISTINCT CONCAT('/repositories/', resource.repo_id, '/resources/', resource.id) SEPARATOR ' | ') AS 'resource_record_uri(s)',
    GROUP_CONCAT(DISTINCT CONCAT('/repositories/', ao.repo_id, '/archival_objects/', ao.id) SEPARATOR ' | ') AS 'resource_component_uri(s)',
    GROUP_CONCAT(DISTINCT COALESCE(resource.title, aor.title) SEPARATOR ' | ') AS 'resource title',
    GROUP_CONCAT(DISTINCT ao.title SEPARATOR ' | ') AS 'component title',
    GROUP_CONCAT(DISTINCT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(accession.identifier, ',', '.'), '"', ''), ']', ''), '[', ''), '.null', '') SEPARATOR ' | ') AS 'accession identifier',
    GROUP_CONCAT(DISTINCT accession.title SEPARATOR ' | ') AS 'accesion title',
    GROUP_CONCAT(DISTINCT COALESCE(r1.repo_code, r2.repo_code, r3.repo_code) SEPARATOR ' | ') AS 'repository',
    GROUP_CONCAT(DISTINCT COALESCE(resource.ead_id, aor.ead_id) SEPARATOR ' | ') AS 'ead id',
    GROUP_CONCAT(DISTINCT COALESCE(ud.string_2, ud2.string_2) SEPARATOR ' | ') AS 'Voyager BIB ID(s)',
    GROUP_CONCAT(DISTINCT ev3.value SEPARATOR ' | ') AS source,
    GROUP_CONCAT(DISTINCT ev1.value SEPARATOR ' | ') AS role,
    GROUP_CONCAT(DISTINCT ev2.value SEPARATOR ' | ') AS relator,
    GROUP_CONCAT(DISTINCT CONVERT( note.notes USING UTF8) SEPARATOR ' | ') AS 'bioghist on agent record',
    CONCAT(@base_public_uri, 'agents/people/', ap.id) AS public_uri,
    ap.publish AS 'agent published?'
FROM
    agent_person ap
        JOIN
    name_person np ON np.agent_person_id = ap.id
        LEFT JOIN
    name_authority_id nai ON np.id = nai.name_person_id
        LEFT JOIN
    linked_agents_rlshp lar ON lar.agent_person_id = ap.id
        LEFT JOIN
    accession ON accession.id = lar.accession_id
        LEFT JOIN
    resource ON resource.id = lar.resource_id
        LEFT JOIN
    archival_object ao ON ao.id = lar.archival_object_id
        LEFT JOIN
    resource aor ON aor.id = ao.root_record_id
        LEFT JOIN
    enumeration_value ev1 ON ev1.id = lar.role_id
        LEFT JOIN
    enumeration_value ev2 ON ev2.id = lar.relator_id
        LEFT JOIN
    enumeration_value ev3 ON ev3.id = np.source_id
        LEFT JOIN
    user_defined ud ON ud.resource_id = resource.id
        LEFT JOIN
    user_defined ud2 ON ud2.resource_id = aor.id
        LEFT JOIN
    note ON note.agent_person_id = ap.id
        LEFT JOIN
    repository r1 ON r1.id = ao.repo_id
        LEFT JOIN
    repository r2 ON r2.id = resource.repo_id
        LEFT JOIN
    repository r3 ON r3.id = accession.repo_id
GROUP BY ap.id
HAVING (all_name_forms LIKE '%mrs.%'
    OR all_name_forms LIKE '%miss %'
    OR all_name_forms LIKE '%miss,%')
ORDER BY np.sort_name
;