you could add count(distinct homebranch) > 1 to your HAVING statement On Fri, Feb 22, 2019 at 9:03 AM Cab Vinton <bibliwho@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