Skip to content

Instantly share code, notes, and snippets.

@ruthtillman
Created September 25, 2017 15:49
Show Gist options
  • Save ruthtillman/2ceefda94d9f7051ca4e5b24636502da to your computer and use it in GitHub Desktop.
Save ruthtillman/2ceefda94d9f7051ca4e5b24636502da to your computer and use it in GitHub Desktop.
Gets records which have family names as agents and returns those. It looks like sometimes it may return the record twice, perhaps if two agents?
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,
name_family.family_name
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
JOIN name_family on linked_agents_rlshp.agent_family_id = name_family.agent_family_id
WHERE
accession_date > '2016-06-30'
AND accession_date < '2017-07-01'
AND repo_id = 3;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment