Cab, would you mind adding that to the 'Catalog/Bibliographic Reports' section of the Koha wiki?
https://wiki.koha-community.org/wiki/SQL_Reports_Library#Catalog.2FBibliographic_Reports I'm sure that others would use that. Thanks! --Barton On Thu, Aug 16, 2018 at 10:33 AM, Cab Vinton <bibli...@gmail.com> wrote: > Many thanks, Barton. I think that may have done it! > > I added an ISBN check (i.isbn not null or empty string) as otherwise > the report was pulling many false duplicates. > > Will have some happy catalogers now :-) > > All best, > > Cab Vinton > Plaistow Public Library > > > On Thu, Aug 16, 2018 at 9:48 AM, Barton Chittenden > <bar...@bywatersolutions.com> wrote: > > > > > > On Tue, Aug 14, 2018, 11:29 AM Cab Vinton <bibli...@gmail.com> wrote: > >> > >> Hi, All -- > >> > >> We're using the following report to identify records w/ duplicate ISBNs. > >> > >> SELECT GROUP_CONCAT(b.biblionumber SEPARATOR ', ') AS biblionumbers, > >> b.title, b.author, GROUP_CONCAT(i.isbn SEPARATOR ', ') AS isbns > >> FROM biblio b > >> LEFT JOIN biblioitems i ON (i.biblionumber=b.biblionumber) > >> GROUP BY CONCAT(substr(b.title,0,9),"/",i.isbn) > >> HAVING COUNT(CONCAT(substr(b.title,0,9),"/",i.isbn))>1 > > > > > > I think you should be able to use > > > > having count(*) > 1 > >> > >> > >> We're a coop, however, so it would be helpful to limit the results to > >> cases where our particular library owns at least one of the associated > >> items. > >> > >> Adding the following lines did not provide correct results, > unfortunately: > >> > >> LEFT JOIN items m ON (m.biblionumber=b.biblionumber) > >> WHERE m.homebranch = <<Library|branches>> > > > > > > I think the join is conflicting with your group by. Try using an exists() > > statement in your where clause: > > > > WHERE EXISTS( > > select * from items where b.biblionumber = items.biblionumber and > > items.homebranch = <<Library|branches>> > > ) > > > > This should be faster than a subquery because it will stop when it hits > the > > first item that makes the query in the exists statement true. > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
