[Koha] Empty shelves

[Paul A]

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 at ccpcs.org>kbrowne at ccpcs.org
>http://www.ccpcs.org/library/
>
>
>On Wed, Apr 16, 2014 at 11:40 AM, Paul A 
><<mailto:paul.a at navalmarinearchive.com>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>http://koha-community.org
><mailto:Koha at lists.katipo.co.nz>Koha at 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>