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 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 suspect a subquery may be required, but it's beyond my current proficiency w/ MySQL :-( Thanks in advance for any guidance. All best, Cab Vinton, Director Plaistow Public Library Plaistow, NH
On Tue, 14 Aug 2018 11:27:54 -0400 Cab Vinton <bibliwho@gmail.com> wrote:
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 suspect a subquery may be required, but it's beyond my current proficiency w/ MySQL :-(
I'm a bit rusty on reports but could <<Library|branches>> be replaced by the code for your library (like 'MAIN' in the sample data) or m.homebranch IN ('BRA1','BRA2') if you want to match multiple branches? And I suspect that LEFT JOIN may be position sensitive but I'm not sure what position would be best for either accuracy or performance. I am probably misunderstanding how/why the attempted change didn't work. Hope that helps, -- MJR http://mjr.towers.org.uk/ Member of http://www.software.coop/ (but this email is my personal view only)
Thank you, MJ. Unfortunately, neither hardcoding the branch location nor changing the location of the JOIN statement changes anything. I think what's happening is that, because of the WHERE condition, my revision is simply a very inefficient way of catching instances where a particular branch owns more than 1 copy of a particular item :-( Figured a subquery would work as a way to first restrict the results to records where Branch A owns at least one copy, & then to perform the search for duplicate records on the resulting subset. All best, Cab Vinton Plaistow Public Library On Wed, Aug 15, 2018 at 7:06 AM, MJ Ray <mjr@phonecoop.coop> wrote:
I'm a bit rusty on reports but could <<Library|branches>> be replaced by the code for your library (like 'MAIN' in the sample data) or m.homebranch IN ('BRA1','BRA2') if you want to match multiple branches?
And I suspect that LEFT JOIN may be position sensitive but I'm not sure what position would be best for either accuracy or performance.
I am probably misunderstanding how/why the attempted change didn't work.
Hope that helps, --
MJR http://mjr.towers.org.uk/ Member of http://www.software.coop/ (but this email is my personal view only)
On Wed, 15 Aug 2018 09:43:50 -0400 Cab Vinton <bibliwho@gmail.com> wrote:
Figured a subquery would work as a way to first restrict the results to records where Branch A owns at least one copy, & then to perform the search for duplicate records on the resulting subset.
Oh I see. So do you mean something like SELECT GROUP_CONCAT(b.biblionumber SEPARATOR ', ') AS biblionumbers, b.title, b.author, GROUP_CONCAT(i.isbn SEPARATOR ', ') AS isbns FROM biblio b WHERE b.biblionumber IN (SELECT m.biblionumber FROM items m WHERE m.homebranch = <<Library|branches>> ) 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 ? That's probably horribly inefficient and I've not tested it because I suspect I've still misunderstood. Hope that helps, -- MJR http://mjr.towers.org.uk/ Member of http://www.software.coop/ (but this email is my personal view only)
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, 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.
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, 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.
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.
On Thu, Aug 16, 2018 at 4:47 PM, Barton Chittenden <barton@bywatersolutions.com> wrote:
Cab, would you mind adding that to the 'Catalog/Bibliographic Reports' section of the Koha wiki?
Done! -- https://wiki.koha-community.org/wiki/SQL_Reports_Library#Duplicate_titles_.2... Cab
Cab++ On Fri, Aug 17, 2018 at 8:17 AM, Cab Vinton <bibliwho@gmail.com> wrote:
On Thu, Aug 16, 2018 at 4:47 PM, Barton Chittenden <barton@bywatersolutions.com> wrote:
Cab, would you mind adding that to the 'Catalog/Bibliographic Reports' section of the Koha wiki?
Done! --
https://wiki.koha-community.org/wiki/SQL_Reports_Library# Duplicate_titles_.28using_title_and_ISBN.29.2C_multi-branch_version
Cab
participants (3)
-
Barton Chittenden -
Cab Vinton -
MJ Ray