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