Thank you helix that gave me a head start.
My query got more complex because I needed to filter only thumbnails as I
have some items that have .jpg files.
And some of them have very generic file names, so I had to match the
thumbnail to the file wihtin a specific ITEM_ID.
This query will detect thumbnails that don't have a respective item anymore:
select i2b.item_id, b.* from bitstream b
inner join bundle2bitstream b2b on b2b.bitstream_id = b.bitstream_id
inner join item2bundle i2b on i2b.bundle_id = b2b.bundle_id
where (b.name like '%.___.jpg')
and rpad(b.name, length(b.name)-4) not in (
select b1.name from bitstream b1
inner join bundle2bitstream b2b1 on b2b1.bitstream_id = b1.bitstream_id
inner join item2bundle i2b1 on i2b1.bundle_id = b2b1.bundle_id
where i2b1.item_id = i2b.item_id
);
Ats,
Alcides Carlos de Moraes Neto
"Sometimes I think we're alone. Sometimes I think we're not. In either
case, the thought is staggering."
- R. Buckminster Fuller
2013/9/15 helix84 <[email protected]>
> Hi Alcides,
>
> you could do something like the following SQL query. Please note that I
> didn't actually test it, it's just to give you the idea.
>
>
> SELECT bistream_id, nameFROM bitstreamWHERE name LIKE '%.jpg'AND rtrim(name,
> '.jpg') NOT IN (SELECT name FROM bitstream);
>
>
> Regards,
> ~~helix84
>
> Compulsory reading: DSpace Mailing List Etiquette
> https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
>
>
>
------------------------------------------------------------------------------
LIMITED TIME SALE - Full Year of Microsoft Training For Just $49.99!
1,500+ hours of tutorials including VisualStudio 2012, Windows 8, SharePoint
2013, SQL 2012, MVC 4, more. BEST VALUE: New Multi-Library Power Pack includes
Mobile, Cloud, Java, and UX Design. Lowest price ever! Ends 9/20/13.
http://pubads.g.doubleclick.net/gampad/clk?id=58041151&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
