I've got the following adapted from one in the reports library. But I need to limit it by item type, or not return everything that doesn't have an item record attached. We've got lots of components in our catalogue that don't have items and it's returning all of them:
SELECT CONCAT( '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', bib.biblionumber, '\">', bib.title, '</a>' ) AS Title, bib.title, bib.biblionumber, lostitems.barcode, lostitems.ccode, lostitems.onloan AS 'Checkout date' FROM biblio bib LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND items.itemlost = 0) LEFT JOIN items AS lostitems ON (bib.biblionumber = lostitems.biblionumber AND lostitems.itemlost != 0) GROUP BY bib.biblionumber HAVING count(items.itemnumber) = 0 Thanks! Elaine Bradtke VWML English Folk Dance and Song Society | http://www.efdss.org Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY Tel +44 (0) 20 7485 2206 (This number is for the English Folk Dance and Song Society in London, England. If you wish to phone me personally, send an e-mail first. I work off site) -------------------------------------------------------------------------- Registered Company No. 297142 Charity Registered in England and Wales No. 305999 _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha