Very late to the party, but maybe this could provide a clue. The following throws an error, but I believe the basic idea of finding records where the lowest itemlost value isn't zero is on the right track:
WHERE itype = <<Item type|itemtypes>> GROUP BY biblionumber HAVING MIN(itemlost) > 0 I suspect SQL is sensitive to the order of these statements, but there are probably other issues as well. Hopefully someone else w/ better report-writing chops than me can take it from there. Or if you found something that works, Elaine, please share w/ the list :-) All best, Cab Vinton, Director Plaistow Public Library Plaistow, NH On Sat, Aug 24, 2019 at 8:30 PM Elaine Bradtke <e...@efdss.org> wrote: > > 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-r...@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: > > > 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 > > > _______________________________________________ > > > 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-r...@inlibro.com <mailto:caroline.cyr-la-r...@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 > > > _______________________________________________ > Koha mailing list http://koha-community.org > Koha@lists.katipo.co.nz > https://lists.katipo.co.nz/mailman/listinfo/koha _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
