Hi Mika I'm not suggesting this is the best way of doing it but this works for me. Just replace the two 'test's with the name of the community.
SELECT name, handle FROM collection col, handle h, (SELECT collection_id FROM community2collection com2col, (SELECT c.community_id FROM community c WHERE c.name = 'test' UNION SELECT child_comm_id FROM community2community WHERE parent_comm_id = (SELECT c.community_id FROM community c WHERE c.name = 'test')) t1 WHERE com2col.community_id = t1.community_id) t2 WHERE col.collection_id = t2.collection_id AND col.collection_id = h.resource_id AND h.resource_type_id = 3; George mikan.d.dspace listmail wrote: > Im trying to create SQL query that would retrieve all collections and > their handles from the given community. This means that collections > under subcommunities of the given root community would be returned as > well. Would someone have a script for this? > > Below is something that I've come up so far. It works fairly well, > allthough it doesnt include the collections under subcommunities of > the given root community. > > create temp view testi as ( > SELECT collection.name, collection.collection_id FROM collection, > community2collection > WHERE ((community2collection.collection_id = collection.collection_id) AND > > (community2collection.community_id IN (SELECT community.community_id > FROM community, > > community2community WHERE ((community2community.child_comm_id = > community.community_id) AND > > (community2community.parent_comm_id = 52))))) ORDER BY collection.name); > select name, handle from testi, handle > where handle.resource_id = testi.collection_id; > > > Thanks for any help, > Mika > > ------------------------------------------------------------------------------ > _______________________________________________ > DSpace-tech mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/dspace-tech > > > -- The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336. ------------------------------------------------------------------------------ _______________________________________________ DSpace-tech mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/dspace-tech
