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