Good point, I removed that logic. 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 DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= dateaccessioned GROUP BY b.biblionumber HAVING isbn != "" AND count(items.homebranch) > 1 ORDER BY rand() LIMIT 7
On Fri, Feb 22, 2019 at 11:23 AM Cab Vinton <bibli...@gmail.com> wrote: > Unless I'm missing the thread somehow, I don't think that will work as > the prior conditions for all 3 branches are still present in the WHERE > statement. > > If we eliminate those, then we still need to include the acquisition > date condition somehow. > > Thanks, > > Cab > > > On Fri, Feb 22, 2019 at 11:11 AM Barton Chittenden > <bar...@bywatersolutions.com> wrote: > > > > 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