[Koha] Items held by more than one branch

[Cab Vinton]

Sorry, no dice -- returns a single item :-(

The mysteries of SQL ...

Cheers,

Cab


On Mon, Apr 23, 2018 at 5:06 PM, Paul Hoffman <paul at flo.org> wrote:
> On Mon, Apr 23, 2018 at 09:19:10AM -0400, Cab Vinton wrote:
>> Trying to write a report that will provide details for items held by
>> more than one branch, with separate output for each library's item.
>
> Put the HAVING clause inside a subquery that determines which bibs'
> items should be included -- something like this (untested):
>
> SELECT b.biblionumber, i.homebranch, t.isbn, b.title, b.author, i.ccode, i.itype
> FROM items i
> LEFT JOIN biblio b USING (biblionumber)
> LEFT JOIN biblioitems t USING (biblionumber)
> WHERE i.itemlost=0 AND i.withdrawn=0
> AND b.biblionumber IN (
>     SELECT b.biblionumber
>     FROM items i
>     LEFT JOIN biblio b USING (biblionumber)
>     WHERE i.itemlost=0 AND i.withdrawn=0
>     HAVING COUNT(i.itemnumber) > 1
> )
> ORDER BY b.title
>
> That's a bunch of data for all items (not lost or withdrawn) on bibs
> that have more than one item (not lost or withdrawn).
>
> HTH,
>
> Paul.
>
> --
> Paul Hoffman <paul at flo.org>
> Software Manager
> Fenway Library Organization
> 550 Huntington Ave.
> Boston, MA 02115
> (617) 442-2384 (FLO main number)
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha