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.2FBibliogra... I'm sure that others would use that. Thanks! --Barton On Thu, Aug 16, 2018 at 10:33 AM, Cab Vinton <bibliwho@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@bywatersolutions.com> wrote:
On Tue, Aug 14, 2018, 11:29 AM Cab Vinton <bibliwho@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,
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
unfortunately: the
first item that makes the query in the exists statement true.