Excerpts from Elaine Bradtke's message of 2019-08-23 11:32:44 -0700:
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
While I'm not an SQL expert by any means, I think I was able to get this to work on my catalog by removing the HAVING clause and putting "WHERE items.itemnumber IS NULL" before the GROUP clause: 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) WHERE items.itemnumber IS NULL GROUP BY bib.biblionumber