[Koha] SQL edit for Non-circulating items report

Mark Alexander marka at pobox.com
Sat Aug 1 08:07:22 NZST 2020


Excerpts from David Sale's message of 2020-07-31 19:12:13 +0000:
> However, adding "b.isbn" to the SELECT list (as with "b.title", "b.author", etc.) breaks the report and renders it non-functional.

You might find this page useful, since it does provide some isbn examples:

https://wiki.koha-community.org/wiki/SQL_Reports_Library

The problem with your query is that isbn is not a field in the biblios
table; it is in the biblioitems table.  You'll need to join that table
to the query, perhaps like the one below.

SELECT bi.isbn, i.itemcallnumber, b.biblionumber, b.title, b.author, b.copyrightdate,
       i.barcode, i.datelastseen, i.issues AS totalcheckouts,
       i.dateaccessioned
FROM items i
LEFT JOIN issues
USING (itemnumber)
LEFT JOIN biblio b
USING (biblionumber)
LEFT JOIN biblioitems bi
USING (biblionumber)
WHERE homebranch = <<Home Library|branches>> AND location = <<Shelving location|LOC>> AND i.withdrawn=0 AND
i.itemlost=0 AND i.damaged=0 AND i.itemnumber NOT IN (SELECT issues.itemnumber FROM issues)
      AND  YEAR(NOW())-YEAR(i.datelastseen) > <<Years NOT seen>>
ORDER BY i.itemcallnumber ASC


More information about the Koha mailing list