ArchivesSpace Query With All Agent IDs or Nulls As Columns

as_agent_accessions.sql

SELECT 
    accession.identifier,
    accession.title,
    
	accession.create_time,
	accession.accession_date,
	accession.general_note,
    date.expression,
    date.begin,
    date.end,
    accession.content_description,
    accession.provenance,
    extent.number,
    enumeration_value.value,
    extent.container_summary,
	user_defined.real_1,
	linked_agents_rlshp.agent_person_id,
	linked_agents_rlshp.agent_corporate_entity_id,
	linked_agents_rlshp.agent_family_id
FROM
    accession
        JOIN
    extent ON accession.id = extent.accession_id
        JOIN
    enumeration_value ON extent.extent_type_id = enumeration_value.id
        JOIN
    date ON accession.id = date.accession_id
		JOIN
	user_defined ON accession.id = user_defined.accession_id
JOIN
linked_agents_rlshp ON accession.id = linked_agents_rlshp.accession_id
WHERE
    accession_date > '2016-06-30' 
        AND accession_date < '2017-07-01' 
        AND repo_id = 3;