Difference between revisions of "SUNScholar/SQL Tips"
Jump to navigation
Jump to search
m |
m |
||
| Line 8: | Line 8: | ||
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 = 'date' AND qualifier = 'issused' ) | ||
SELECT * FROM metadatavalue WHERE metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'contributor' AND qualifier = 'advisor' ) | 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; | ||
Revision as of 15:44, 22 September 2014
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;
