[Koha] Items held by more than one branch

[Joy Nelson]

Cab -
This is a little (ok..a lot) quick and dirty, but does return results...
does this help?

SELECT i.biblionumber,b.title,
homebranch,itemcallnumber,barcode,itype,location,ccode,itemlost,withdrawn
FROM items i
LEFT JOIN biblio b USING (biblionumber)
where biblionumber in (select biblionumber from items  group by
biblionumber having count(distinct(homebranch)) >1)
order by i.biblionumber




On Mon, Apr 23, 2018 at 5:39 PM, Cab Vinton <bibliwho at gmail.com> wrote:

> 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
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>



-- 
Joy Nelson
Vice President of Implementations

ByWater Solutions <http://bywatersolutions.com>
Support and Consulting for Open Source Software
Phone/Fax (888)900-8944
What is Koha? <http://bywatersolutions.com/what-is-koha/>