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