Try the following...
select
c2c.community_id,
cmh.handle,
i.owning_collection,
ch.handle chandle,
i.item_id,
ih.handle ihandle
from
item i
inner join handle ch
on ch.resource_id=i.owning_collection
and ch.resource_type_id=3
inner join handle ih
on ih.resource_id = i.item_id
and ih.resource_type_id = 2
inner join community2collection c2c
on c2c.collection_id = i.owning_collection
inner join handle cmh
on cmh.resource_id = c2c.community_id
and cmh.resource_type_id = 4
On Tue, Dec 2, 2014 at 12:32 PM, bender <[email protected]>
wrote:
> Hi,
>
> I need to obtain report with a complete list of the Communities with their
> respective Handle number, and for each Community I need a list of their
> Collections, with their respective Handle number, and for each Collection I
> need a list of their Items, with their respective Handle number.
>
> Ej:
>
> Community 1, 123456789/1
>
> - Collection 1, 123456789/3
>
> - Item 1, 123456789/40
>
> - Item 2, 123456789/41
>
> - ...
>
> - Collection 2, 123456789/4
>
> - Item 3, 123456789/43
>
> - Item 4, 123456789/44
>
> - ...
>
> - ....
>
> Community 2, 123456789/2
>
> - Collection 1, 123456789/10
>
> - Item 5, 123456789/45
>
> - Item 6, 123456789/46
>
> - ...
>
> - Collection 2, 123456789/11
>
> - Item 1, 123456789/47
>
> - Item 2, 123456789/48
>
> - ...
>
> - ....
>
>
> I understand this is only possible with a direct Query to the database.
> I've reviewed the ER database model, but I've difficulties to figure out
> how to design the query. I'm not an expert and need to get this report as
> soon as possible.
>
> Can anyone help me?
>
> Thanks in advance.
>
> Bender
>
>
> ------------------------------------------------------------------------------
> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
> with Interactivity, Sharing, Native Excel Exports, App Integration & more
> Get technology previously reserved for billion-dollar corporations, FREE
>
> http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
> _______________________________________________
> DSpace-tech mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/dspace-tech
> List Etiquette:
> https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
>
--
Terry Brady
Applications Programmer Analyst
Georgetown University Library Information Technology
https://www.library.georgetown.edu/lit/code
425-298-5498
------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
_______________________________________________
DSpace-tech mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/dspace-tech
List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
