[Koha] Report help needed - lost items
Mark Alexander
marka at pobox.com
Sat Aug 24 07:00:14 NZST 2019
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
More information about the Koha
mailing list