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.