Mark, that still didn't do the trick, but thanks! Caroline, this is much closer to what I need. But still not quite right. It's messy because I've altered a report that I found in the report library rather inelegantly. What I really need is a list of titles and biblio numbers of biblios where the last item is lost, or missing, or withdrawn (I can change that part of the query myself). I have to limit it by format because we have articles and components in the catalogue that don't have item records attached. What I'm getting now lis a list of biblios with items that are missing but it includes biblios that still have one or more items that are on the shelf (but at least one item that is missing). Or in other words, I'm looking for biblios where the all the items are missing/lost/withdrawn. Many thanks for your help! Elaine Bradtke VWML English Folk Dance and Song Society | http://www.efdss.org Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY Tel +44 (0) 20 7485 2206 (This number is for the English Folk Dance and Song Society in London, England. If you wish to phone me personally, send an e-mail first. I work off site) -------------------------------------------------------------------------- Registered Company No. 297142 Charity Registered in England and Wales No. 305999 On Fri, Aug 23, 2019 at 12:30 PM Caroline Cyr-La-Rose < caroline.cyr-la-rose@inlibro.com> wrote:
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> _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha