SUNScholar/SQL Tips/5.X

BACK TO SQL TIPS SELECT text_value FROM metadatavalue WHERE metadatavalue.resource_type_id=2 AND metadatavalue.metadata_field_id=64 select h.handle, regexp_replace(t.text_value,E'[\r\n\t ]+',' ','g') as title from item i2 inner join metadatavalue t on t.resource_id = i2.item_id and t.resource_type_id = 2 inner join metadatafieldregistry mfr on t.metadata_field_id=mfr.metadata_field_id and mfr.element='title' inner join handle h on h.resource_id = i2.item_id and h.resource_type_id = 2 where not exists (select 1 from item2bundle i2b inner join bundle b on i2b.bundle_id = b.bundle_id where i2.item_id = i2b.item_id) SELECT cm.community_id, co.collection_id, substring(mv.text_value,1,7) AS year_month, count(*) AS num_items_added FROM metadatavalue mv, item it, collection co, community cm, community2collection c2c WHERE mv.resource_id = it.item_id AND it.owning_collection = co.collection_id AND co.collection_id = c2c.collection_id AND c2c.community_id = cm.community_id AND mv.metadata_field_id = 11 AND it.in_archive AND text_value >= '2016-02' AND text_value < '2016-03' GROUP BY 1, 2, 3 ORDER BY 2;
 * List of titles
 * Search and delete metadata
 * SELECT text_value FROM metadatavalue WHERE resource_type_id=2 AND ... [YourOtherClauses)
 * select resource_id from metadatavalue where resource_type_id=2 and metadata_field_id=86;
 * select resource_id from metadatavalue where resource_type_id=2 and metadata_field_id=78 and (text_value='' OR text_value IS NULL);
 * delete from metadatavalue where metadata_field_id=78 and text_value='';
 * Find items with missing bitstreams
 * IR Growth by month