SUNScholar/SQL Tips

Back to System Admin

Below are SQL statements to be used for various purposes.

Extract metadata

• SELECT * FROM metadatavalue WHERE metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'date' AND qualifier = 'issused' )
• SELECT * FROM metadatavalue WHERE metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'contributor' AND qualifier = 'advisor' )

Find short dates

• SELECT * FROM metadatavalue,metadatafieldregistry WHERE metadatavalue.metadata_field_id = metadatafieldregistry.metadata_field_id AND element = 'date' AND qualifier = 'accessioned' AND CHAR_LENGTH(text_value) < 10;

Empty date

• SELECT item_id FROM metadatavalue WHERE metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'date' AND qualifier = 'accessioned' ) AND text_value = ;

Empty type

• SELECT item_id FROM metadatavalue WHERE metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'type') AND text_value = ;