you could add count(distinct homebranch) > 1
to your HAVING statement On Fri, Feb 22, 2019 at 9:03 AM Cab Vinton <bibli...@gmail.com> wrote: > Hi, All -- > > We have a report (see below) that generates a random list of items > held by all 3 of our branches & purchased within the past 30 days. > > To generate more titles we'd like to loosen the criteria by specifying > that the titles should be held by just 2 of the 3 branches. > > I know how I'd approach this outside of Koha's SQL-based reporting > tool, but not sure how to write this without repeating a whole bunch > of code ((A or B) or (A or C) or (B or C)). The code is used to > populate a carousel on our OPAC's home page & it's already a bit > "pokey", so efficient code is an important factor. > > Anyone have suggestions on how best to write this report? > > Many thanks in advance, > > Cab Vinton, Director > Plaistow Public Library > Plaistow, NH > https://catalog.southernnh.bywatersolutions.com > > > SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, ' ', 1) AS isbn, > b.title, i.homebranch, i.itemcallnumber > FROM items i > LEFT JOIN biblioitems m USING (biblioitemnumber) > LEFT JOIN biblio b ON i.biblionumber = b.biblionumber > WHERE i.itype = 'NEWBOOK' > AND b.biblionumber IN (SELECT biblionumber FROM items WHERE > DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= dateaccessioned AND > homebranch='KIMBALL') > AND b.biblionumber IN (SELECT biblionumber FROM items WHERE > DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= dateaccessioned AND > homebranch='PLAISTOW') > AND b.biblionumber IN (SELECT biblionumber FROM items WHERE > DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= dateaccessioned AND > homebranch='SANDOWN') > GROUP BY b.biblionumber > HAVING isbn != "" > ORDER BY rand() > LIMIT 7 > _______________________________________________ > 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
