[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