<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:"Calibri Light";
panose-1:2 15 3 2 2 2 4 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri Light",sans-serif;
color:windowtext;
font-weight:normal;
font-style:normal;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span style="color:#1F497D">Danielle,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">This query supplies the resource id, identifier, title, and extent (i.e. number, value, container summary, physical details and dimension):<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">SELECT resource.id, resource.identifier, resource.title, extent.number, enumeration_value.value, extent.container_summary, extent.physical_details, extent.dimensions<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">FROM (resource INNER JOIN extent ON resource.id = extent.resource_id) INNER JOIN enumeration_value ON extent.extent_type_id = enumeration_value.id;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">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<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">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;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">--Miloche<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:Symbol;color:#1F497D">»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»</span><span style="font-size:10.0pt;color:#1F497D"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;color:#1F497D">Miloche Kottman<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;color:#1F497D">Head of Cataloging & Archival Processing<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;color:#1F497D">University of Kansas Libraries<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;color:#1F497D">Lawrence, KS 66045<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;color:#1F497D"><a href="mailto:mkottman@ku.edu"><span style="color:blue">mkottman@ku.edu</span></a><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;color:#1F497D">785-864-3916<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b>From:</b> archivesspace_users_group-bounces@lyralists.lyrasis.org <archivesspace_users_group-bounces@lyralists.lyrasis.org>
<b>On Behalf Of </b>Danielle Butler<br>
<b>Sent:</b> Monday, February 25, 2019 5:06 PM<br>
<b>To:</b> Archivesspace Users Group <archivesspace_users_group@lyralists.lyrasis.org><br>
<b>Subject:</b> [Archivesspace_Users_Group] Extent Total<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif">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?
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif">Thanks,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif;color:#1F4E79">Danielle Butler, CA | Archivist<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif">Butler Center for Arkansas Studies | Central Arkansas Library System
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif"><a href="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">www.butlercenter.org</a>
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif">100 Rock Street<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif">Little Rock, AR 72201<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Calibri Light",sans-serif">501-320-5724<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>