Re: [Koha] Report help needed - lost items

23 Aug 2019

      Hello Elaine,

from what I understand, you want bibliographic information on lost items?

I'd need to know exactly the purpose of the report to help you more as 
there are things in there I don't understand (like why select barcode, 
when afterwards you group by biblionumber, or why the title is there twice).

First, instead of selecting from the biblio table, I would go the other 
way and select from the items table. That way, you are sure of getting 
only things that have an item.

If you want to limit by itemtype, you can add WHERE itype = <<Item 
type|itemtypes>> in your query to have the ability to choose your 
itemtype upon executing your report.

I cleaned your query up a little bit, but like I mentioned before, as I 
don't know the purpose, it might not be what you need.

SELECT
         CONCAT(
             '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',
             biblionumber,
             '\">',
             title,
             '</a>'
         ) AS Title,
         biblionumber,
         barcode,
         ccode,
         onloan AS 'Checkout date'
FROM
         items
         JOIN biblio USING (biblionumber)
WHERE itemlost != 0
       AND itype = <<Item type|itemtypes>>

Let us know if this works.
Caroline

On 19-08-23 15 h 00, Mark Alexander wrote:
...
...
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
Excerpts from Elaine Bradtke's message of 2019-08-23 11:32:44 -0700:
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
_______________________________________________
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha
-- 
Caroline Cyr La Rose, M.S.I.
Bibliothécaire | Responsable de produit

Tél. : 1-833-465-4276, poste 221
Caroline.Cyr-La-Rose@inLibro.com <mailto:caroline.cyr-la-rose@inLibro.com>

INLiBRO | Spécialistes en technologies documentaires | www.inLibro.com 
<http://www.inLibro.com>

Re: [Koha] Report help needed - lost items

Caroline Cyr-La-Rose