[Koha] Duplicate ISBN report modification

[Barton Chittenden]

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 <bibliwho at 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
> <barton at bywatersolutions.com> wrote:
> >
> >
> > On Tue, Aug 14, 2018, 11:29 AM Cab Vinton <bibliwho at 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.
>