[Koha] Duplicate ISBN report modification

[Cab Vinton]

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.