At 08:57 AM 4/22/2014 -0400, Katelyn Browne wrote:

Hi Paul, Not sure if you've solved this yet--we've been on spring break! I got your report to work by changing the order of the tables joined together. By pulling from authorised_values first (and joining everything to that), you get all possible location values rather than just the ones that appear in biblioitems.

Hi Katelyn, Many thanks for your reply. With a little assistance and some refining, we ended up with a good working solution (in fact biblioitems was an unnecessary complication) : SELECT authorised_value AS Auth_Location, COUNT(items.itemnumber) FROM authorised_values LEFT JOIN items ON (authorised_values.authorised_value=items.location) WHERE authorised_values.category LIKE 'loc' GROUP BY authorised_value HAVING COUNT(DISTINCT(items.itemnumber))=0; Best regards and again thanks, Paul

Here's a report that worked for me:

SELECT authorised_values.authorised_value AS Auth_Location, COUNT(DISTINCT biblioitems.biblionumber) as Quantity FROM authorised_values LEFT JOIN items ON (authorised_values.authorised_value=items.location) LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) WHERE authorised_values.category LIKE 'loc' GROUP BY authorised_values.authorised_value HAVING COUNT(DISTINCT biblioitems.biblionumber)=0

--Katelyn.

Katelyn Browne Middle/High School Librarian Capital City Public Charter School 100 Peabody Street NW Washington, DC 20011 (202) 387-0309 x1745 <mailto:kbrowne@ccpcs.org>kbrowne@ccpcs.org http://www.ccpcs.org/library/

On Wed, Apr 16, 2014 at 11:40 AM, Paul A <<mailto:paul.a@navalmarinearchive.com>paul.a@navalmarinearchive.com> wrote: Help requested, please -- the MySQL part of my brain is obviously not at the right caffeine level.

Over the years, we have used a lot of "shelves" (authorized values, mostly temporary boxes) and now I need to identify the "empty" ones as a cleanup/caretaking excercise.

The following SQL query works syntactically, and finds any shelf with at least one item:

SELECT authorised_values.authorised_value AS Auth_Location, COUNT(DISTINCT biblioitems.biblionumber) as Quantity FROM biblioitems LEFT JOIN items ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN authorised_values ON (authorised_values.authorised_value=items.location) WHERE authorised_values.category LIKE 'loc' GROUP BY authorised_values.authorised_value HAVING COUNT(DISTINCT biblioitems.biblionumber)>0;

but if I change the final >0 to =0 it fails to find any empty box ;={

I'm sort-of convinced that this comes from JOINing the authorised_values.authorised_value to the items.location (obviously that location no longer exists in items), but am going in circles looking for an alternate method.

Thanks in advance -- Paul

_______________________________________________ Koha mailing list <http://koha-community.org>http://koha-community.org <mailto:Koha@lists.katipo.co.nz>Koha@lists.katipo.co.nz <http://lists.katipo.co.nz/mailman/listinfo/koha>http://lists.katipo.co.nz/mailman/listinfo/koha

--- Maritime heritage and history, preservation and conservation, research and education through the written word and the arts. <http://NavalMarineArchive.com> and <http://UltraMarine.ca>