A slight change in the 'community' query --resource_type_id for communities is 4 [1]
SELECT h.handle, c.name FROM ( (SELECT resource_id, handle FROM handle WHERE resource_type_id=4) as h JOIN (SELECT community_id, name FROM community) as c ON h.resource_id = c.community_id ); [1] https://github.com/DSpace/DSpace/blob/master/dspace-api/src/main/java/org/dspace/core/Constants.java#L29 Lighton Phiri http://lightonphiri.org On 12 March 2013 19:40, Lighton Phiri <lighton.ph...@gmail.com> wrote: > You need to join handle table with the collection and/or community > tables. Just include a WHERE clause in either one of the queries > below.. > > COLLECTION > > SELECT > h.handle, > c.name > FROM > ( > (SELECT resource_id, handle FROM handle WHERE resource_type_id=3) as h JOIN > (SELECT collection_id, name FROM collection) as c ON > h.resource_id = c.collection_id > ); > > > COMMUNITY > > SELECT > h.handle, > c.name > FROM > ( > (SELECT resource_id, handle FROM handle WHERE resource_type_id=3) as h JOIN > (SELECT community_id, name FROM community) as c ON > h.resource_id = c.community_id > ); > > > > Lighton Phiri > http://lightonphiri.org > > > On 12 March 2013 18:45, Drover, Matt <mattdro...@mun.ca> wrote: >> How do I get the handle for communities and collections, providing I have >> the community_id or collection_id ? I'm not seeing a table or field that >> links the two in the database. >> >> >> >> Thanks. >> >> >> >> Matthew Drover >> Programmer Consultant >> >> Immersive Technology >> Distance Education, Learning and Teaching Support >> >> Memorial University of Newfoundland >> >> >> >> >> This electronic communication is governed by the terms and conditions at >> http://www.mun.ca/cc/policies/electronic_communications_disclaimer_2012.php >> >> ------------------------------------------------------------------------------ >> Symantec Endpoint Protection 12 positioned as A LEADER in The Forrester >> Wave(TM): Endpoint Security, Q1 2013 and "remains a good choice" in the >> endpoint security space. For insight on selecting the right partner to >> tackle endpoint security challenges, access the full report. >> http://p.sf.net/sfu/symantec-dev2dev >> _______________________________________________ >> DSpace-tech mailing list >> DSpace-tech@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/dspace-tech >> List Etiquette: >> https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette ------------------------------------------------------------------------------ Symantec Endpoint Protection 12 positioned as A LEADER in The Forrester Wave(TM): Endpoint Security, Q1 2013 and "remains a good choice" in the endpoint security space. For insight on selecting the right partner to tackle endpoint security challenges, access the full report. http://p.sf.net/sfu/symantec-dev2dev _______________________________________________ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
