[Koha] Items held by more than one branch

[Cab Vinton]

Thank you, Joy -- I think that did the trick. Nice work!

Cab


On Mon, Apr 23, 2018 at 7:09 PM, Joy Nelson <joy at bywatersolutions.com> wrote:
> 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
> Support and Consulting for Open Source Software
> Phone/Fax (888)900-8944
> What is Koha?
>