[Koha] Items held by more than one branch

[Cab Vinton]

Thank you, Jonathan.

That fixes one problem (output now appears to be for the right bib
numbers), but the GROUP function introduces another issue -- namely, it
collapses the results so that just one item is show for each bib number:

14820 PLAISTOW 1572151714 : '20s & '30s style / Horsham, Michael. NONFICTION
BOOK

when what I originally was after was:

14820 PLAISTOW 1572151714 : '20s & '30s style / Horsham, Michael. NONFICTION
BOOK
14820 KIMBALL    1572151714 : '20s & '30s style / Horsham, Michael.
NONFICTION BOOK

Looks like I'll have to find a way to include information for all items on
that single line. We have 3 branches, so output would vary depending on
whether a particular item is owned by just 2 or all 3 libraries.

(Btw, I know the above is redundant, but ultimately I'll be adding fields
that are not the same -- price, ordering source, etc.)

Thanks again,

Cab Vinton



On Mon, Apr 23, 2018 at 10:14 AM, Jonathan Druart <
jonathan.druart at bugs.koha-community.org> wrote:

> I'd say you are missing a group by.
>
> On Mon, 23 Apr 2018 at 10:19 Cab Vinton <bibliwho at gmail.com> wrote:
>
>> Hi, all--
>>
>> Another report question...
>>
>> 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.
>>
>> Including the statement HAVING COUNT(i.itemnumber) > 1 collapses the
>> results to just one title. (Strangely, the bib returned only has a
>> single item attached.)
>>
>> I'm missing some basic feature of how SQL operates :-(
>>
>> 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
>> HAVING COUNT(i.itemnumber) > 1
>> ORDER BY b.title
>>
>> Grateful in advance for any assistance!
>>
>> All best,
>>
>> Cab Vinton
>> Plaistow Public Library
>> Plaistow, NH
>> _______________________________________________
>> Koha mailing list  http://koha-community.org
>> Koha at lists.katipo.co.nz
>> https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>