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@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@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@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha