[Koha] Duplicate ISBN report modification

[Cab Vinton]

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
_______________________________________________
Koha mailing list http://koha-community.org
***@lists.katipo.c

[MJ Ray]

On Tue, 14 Aug 2018 11:27:54 -0400

Post by Cab Vinton
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 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,

[Cab Vinton]

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

Post by MJ Ray
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)

_______________________________________________
Koha mailing list http://koha-community.org
***@lists.katipo.co.nz
https://lists.katip

[MJ Ray]

On Wed, 15 Aug 2018 09:43:50 -0400

Post by Cab Vinton
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,

[Barton Chittenden]

Post by Cab Vinton
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

Post by Cab Vinton
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.
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
***@lists.katipo.co.

[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

Post by Barton Chittenden

Post by Cab Vinton
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

Post by Cab Vinton
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.
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()
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
***@lists.katipo.co

[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

Post by 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

Post by Barton Chittenden

Post by Cab Vinton
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

Post by Cab Vinton
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()
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

Post by Barton Chittenden
first item that makes the query in the exists statement true.

_______________________________________________
Koha mailing list http://koha-community.org
***@lists.katipo.co.nz
https://lists.katipo.

[Cab Vinton]

On Thu, Aug 16, 2018 at 4:47 PM, Barton Chittenden

Post by Barton Chittenden
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
_______________________________________________
Koha mailing list http://koha-community.org
***@lists.katipo.co.nz
https://l

[Barton Chittenden]

Cab++

Post by Cab Vinton
On Thu, Aug 16, 2018 at 4:47 PM, Barton Chittenden

Post by Barton Chittenden
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

_______________________________________________
Koha mailing list http://koha-community.org
***@lists.katipo.co.nz
https://lists.katipo.