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 _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha