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 <bibliwho@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 <barton@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 <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