The ISBN is in the biblioitems table. Change b.isbn to bi.isbn and add
LEFT JOIN biblionitems bi USING (biblionumber) before the line that
starts with WHERE.
On 7/31/20 2:12 PM, David Sale wrote:
Hello,
Our library is engaged in a weeding project that requires a SQL report for Koha
to be customized.
The report we are attempting to customize was written by ByWater Solutions, and it
generates a list of items "not seen" in X years, by branch and shelf location.
The existing report is written in SQL as follows:
SELECT 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)
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
We would like to modify this report so that in addition to returning the
author, title and copyright date, it also returns the ISBN.
However, adding "b.isbn" to the SELECT list (as with "b.title", "b.author",
etc.) breaks the report and renders it non-functional.
Can anyone suggest how the SQL given above should be modified to create a functional
report that will generate a list of items "not seen" in X years, by branch and
shelf location, and that will return the ISBN *in addition to* the title, author,
copyright date and barcode for each item listed in the report?
Thanks,
David Sale
Coastline Library Network
Coos/Curry Counties, Oregon
_______________________________________________
Koha mailing list http://koha-community.org
[email protected]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
--
Tim McMahon
West Liberty Public Library
319-627-2084
_______________________________________________
Koha mailing list http://koha-community.org
[email protected]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
