[Archivesspace_Users_Group] Extent Total

[Kottman, Miloche]

Danielle,

I use MS Access to run my ArchivesSpace queries but the SQL should be easy to tweak for whatever you're using to run your queries.

This query supplies the resource id, identifier, title, and extent (i.e. number, value, container summary, physical details and dimension):

SELECT resource.id, resource.identifier, resource.title, extent.number, enumeration_value.value, extent.container_summary, extent.physical_details, extent.dimensions
FROM (resource INNER JOIN extent ON resource.id = extent.resource_id) INNER JOIN enumeration_value ON extent.extent_type_id = enumeration_value.id;

Your message mentioned something about accessions so here's a second query that shows the extents for ALL accessions with resource information (i.e. title, identifier, extent) IF there is a linked resource record.  So if there's a resource record that does not have an accession record, it won't show up in this query.  And if an accession is linked to multiple resource records, it will display multiple times.  FYI-I think the table you may be missing in your queries to link accessions to resources is the "spawned_rlshp" table.

SELECT accession.id AS AccessionID, accession.identifier AS AccessionIdentifier, accession.title AS AccessionTitle, extent.number, enumeration_value.value, extent.container_summary, extent.physical_details, extent.dimensions, resource.id AS ResourceID, resource.title AS ResourceTitle, resource.identifier AS ResourceIdentifier, extent_1.number, enumeration_value_1.value, extent_1.container_summary, extent_1.physical_details, extent_1.dimensions
FROM ((((accession LEFT JOIN (spawned_rlshp LEFT JOIN resource ON spawned_rlshp.resource_id = resource.id) ON accession.id = spawned_rlshp.accession_id) INNER JOIN extent ON accession.id = extent.accession_id) INNER JOIN enumeration_value ON extent.extent_type_id = enumeration_value.id) LEFT JOIN extent AS extent_1 ON resource.id = extent_1.resource_id) LEFT JOIN enumeration_value AS enumeration_value_1 ON extent_1.extent_type_id = enumeration_value_1.id;

--Miloche

******************************
Miloche Kottman
Head of Cataloging & Archival Processing
University of Kansas Libraries
Lawrence, KS 66045
mkottman at ku.edu<mailto:mkottman at ku.edu>
785-864-3916



From: archivesspace_users_group-bounces at lyralists.lyrasis.org <archivesspace_users_group-bounces at lyralists.lyrasis.org> On Behalf Of Danielle Butler
Sent: Monday, February 25, 2019 5:06 PM
To: Archivesspace Users Group <archivesspace_users_group at lyralists.lyrasis.org>
Subject: [Archivesspace_Users_Group] Extent Total

I am trying to query our MySQL database to get a list of resource identifiers with corresponding extent numbers. I have been fighting with it for a while and I can get a list of extent numbers with no associated resource information with included extents for accessions. With no apparent way to discern which is which (I don't want both accessions and resource extent data because the data with be duplicative for processed collections). I can get a list of resources, but without extent info. Does anyone know of a way to get both out of the database?

Thanks,

Danielle Butler, CA | Archivist
Butler Center for Arkansas Studies | Central Arkansas Library System
www.butlercenter.org<https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.butlercenter.org%2F&data=02%7C01%7Cmkottman%40ku.edu%7C694dec777a1c4fdc0d5408d69b75c3cc%7C3c176536afe643f5b96636feabbe3c1a%7C0%7C1%7C636867327430853345&sdata=LWojTN6HvYDpmd4ZYHMqGSjJJgYJtZRrkLWSZvKS3Dk%3D&reserved=0>
100 Rock Street
Little Rock, AR 72201
501-320-5724

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lyralists.lyrasis.org/pipermail/archivesspace_users_group/attachments/20190226/4fb84ce9/attachment.html>