AccessionsAgentSource.sql

SELECT 
    CONCAT('https://archivesspace.smith.edu/accessions/',
            accession.id) 'accession URL',
    repository.repo_code repository,
    accession.title 'accession title',
    accession.identifier 'accession ID',
    accession.accession_date 'accession date',
    accession.create_time 'record creation date',
    accession.created_by 'record creator',
    CONCAT(name_person.primary_name,
            ', ',
            name_person.rest_of_name) 'agent person name',
    name_corporate_entity.primary_name 'agent corporate name',
    evrole.value 'agent type',
    evrelator.value 'MARC relator code'
FROM
    accession
        JOIN
    linked_agents_rlshp ON linked_agents_rlshp.accession_id = accession.id
        LEFT JOIN
    agent_person ON linked_agents_rlshp.agent_person_id = agent_person.id
        LEFT JOIN
    agent_corporate_entity ON linked_agents_rlshp.agent_corporate_entity_id = agent_corporate_entity.id
        LEFT JOIN
    name_person ON name_person.agent_person_id = agent_person.id
        LEFT JOIN
    name_corporate_entity ON name_corporate_entity.agent_corporate_entity_id = agent_corporate_entity.id
        LEFT JOIN
    enumeration_value evrole ON linked_agents_rlshp.role_id = evrole.id
        LEFT JOIN
    enumeration_value evrelator ON linked_agents_rlshp.relator_id = evrelator.id
        LEFT JOIN
    repository ON accession.repo_id = repository.id
WHERE
    evrole.value = 'source';