On Tue, Aug 14, 2018, 11:29 AM Cab Vinton <bibli...@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.
_______________________________________________
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha
