Sample ASpace SQL query to get Resource information and their extent statements

aspace-resource-extent-example.sql

SELECT 
    r.id AS 'resource database id',
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(r.identifier, ',', '.'),'"',''),']',''),'[',''),'.null','') AS 'collection call number',
    r.title,
    r.publish,
    r.repo_id,
    r.ead_id,
    r.finding_aid_author,
    GROUP_CONCAT(DISTINCT extent.number, ' ', ev.value
        SEPARATOR '; ') AS 'Extent',
    GROUP_CONCAT(DISTINCT extent.container_summary
        SEPARATOR '; ') AS 'Additional Extent',
    GROUP_CONCAT(DISTINCT extent.dimensions
        SEPARATOR '; ') AS 'Extent Dimensions',
    GROUP_CONCAT(DISTINCT extent.physical_details
        SEPARATOR '; ') AS 'Physical Details'
FROM
    resource r
        LEFT JOIN
    extent ON extent.resource_id = r.id
        LEFT JOIN
    enumeration_value ev ON extent.extent_type_id = ev.id
GROUP BY r.id
ORDER BY r.ead_id
;