[Koha] Empty shelves

[Katelyn Browne]

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.

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
kbrowne at ccpcs.org
http://www.ccpcs.org/library/


On Wed, Apr 16, 2014 at 11:40 AM, Paul A <paul.a at 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
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>