[Koha] Report help needed - lost items

[Mark Alexander]

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