Hi everyone, Just a follow up for the community :)
Thanks to the savvy brain trust that is the Koha list, I am now successfully using the following report to query ISBNs (+biblionumber, +title as required by the coverflow plugin) based on subject: SELECT DISTINCT biblioitems.isbn,biblio.title,biblio.biblionumber, c.imagenumber AS localcover, ExtractValue(biblio_metadata.metadata,'//datafield[@tag="650"]/subfield[@code>="a"]') AS Subject FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber = biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber) LEFT JOIN biblio_metadata ON (biblio_metadata.biblionumber = biblio.biblionumber) LEFT JOIN biblioimages c ON (items.biblionumber=c.biblionumber) WHERE items.dateaccessioned BETWEEN "2000-01-01" AND CURDATE() AND ExtractValue(biblio_metadata.metadata,'//datafield[@tag="650"]/subfield[@code>="a"]') LIKE "%Art%" AND (isbn IS NOT NULL OR isbn !='') This report also includes any local cover that has been uploaded to a bib record. Could be useful in cases where libraries have not created a custom fallback cover for records without a cover. You'll also notice that the choice of subject is hard-coded into the report on the second last line. In this case, I chose %Art% to capture any book that has a subject with the word 'Art' in it. Final result: http://library.craigbutosi.ca/ Again, thanks to everyone. Craig Butosi, MA, MLIS, B Mus (Hons) Library: library.craigbutosi.ca On Tue, Mar 19, 2019 at 11:49 AM Craig Butosi <cbut...@gmail.com> wrote: > Hi all, > > Holger, it looks your syntax works. I've modified your report to reflect a > few other things. I managed to get the following to work successfully: > > SELECT * FROM > ( > SELECT > biblioitems.isbn, > items.barcode, > items.itemcallnumber, > biblio.title, > biblio.author, > > ExtractValue(biblio_metadata.metadata,'//datafield[@tag="650"]/subfield[@code>="a"]') > AS Subject > FROM items > LEFT JOIN biblioitems ON (items.biblioitemnumber = > biblioitems.biblioitemnumber) > LEFT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber) > LEFT JOIN biblio_metadata ON (biblio_metadata.biblionumber = > biblio.biblionumber) > WHERE > items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>> > AND <<and (yyyy-mm-dd)|date>> > ) AS t > WHERE > Subject LIKE concat('%',<<Subject>>,'%') AND isbn IS NOT NULL AND isbn > != '' > LIMIT 20 > > Thanks to all of you for your assistance. > > > Craig Butosi, MA, MLIS, B Mus (Hons) > Library: library.craigbutosi.ca > > > On Tue, Mar 19, 2019 at 11:10 AM Holger Meissner < > holger.meiss...@hs-gesundheit.de> wrote: > >> Hi Craig, >> >> does it work like this? >> >> SELECT * FROM >> ( >> SELECT >> items.dateaccessioned, >> items.barcode, >> items.itemcallnumber, >> biblio.title, >> biblio.author, >> biblioitems.publishercode, >> >> ExtractValue(biblio_metadata.metadata,'//datafield[@tag="650"]/subfield[@code>="a"]') >> AS Subject >> FROM items >> LEFT JOIN biblioitems ON (items.biblioitemnumber = >> biblioitems.biblioitemnumber) >> LEFT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber) >> LEFT JOIN biblio_metadata ON (biblio_metadata.biblionumber = >> biblio.biblionumber) >> WHERE >> items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>> >> AND <<and (yyyy-mm-dd)|date>> >> ) AS t >> WHERE >> Subject LIKE concat('%',<<Subject>>,'%') >> ORDER BY dateaccessioned DESC >> >> Regards, >> Holger >> >> -----Ursprüngliche Nachricht----- >> Von: Koha <koha-boun...@lists.katipo.co.nz> Im Auftrag von Craig Butosi >> Gesendet: Dienstag, 19. März 2019 15:23 >> An: Barton Chittenden <bar...@bywatersolutions.com> >> Cc: koha <koha@lists.katipo.co.nz> >> Betreff: Re: [Koha] Koha 18.11 - SQL report to select ISBNs by Subject >> >> Hi everyone, >> >> Mohammad, Barton, thanks very much for this. Unfortunately, I've already >> tried this report (I found it on the Koha SQL reports page online) and it >> returns the following error (even after I replace the "biblioitems.marcxml" >> statement with "biblio_metadata.metadata"): >> >> *The following error was encountered:* >> The database returned the following error: >> Unknown column 'biblio_metadata.metadata' in 'field list' >> Please check the log for further details. >> >> which is strange. Here's what my report looks like: >> >> SELECT * >> FROM(SELECT >> items.dateaccessioned, >> items.barcode, >> items.itemcallnumber, >> biblio.title, >> biblio.author, >> biblioitems.publishercode, >> (SELECT >> >> ExtractValue(biblio_metadata.metadata,'//datafield[@tag="650"]/subfield[@code>="a"]')) >> AS Subject >> FROM items >> LEFT JOIN biblioitems ON >> (items.biblioitemnumber=biblioitems.biblioitemnumber) >> LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) >> WHERE items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>> AND >> <<and >> (yyyy-mm-dd)|date>>) AS t >> WHERE Subject LIKE concat('%',<<Subject>>,'%') ORDER BY dateaccessioned >> DESC >> >> No errors in /var/log/mysql either. >> >> Any ideas? >> >> Many thanks, >> >> Craig Butosi, MA, MLIS, B Mus (Hons) >> Library: library.craigbutosi.ca >> >> >> On Tue, Mar 19, 2019 at 8:42 AM Barton Chittenden < >> bar...@bywatersolutions.com> wrote: >> >> > The data formerly found in biblioitems.xml moved to >> > biblio_metadata.metadata in Koha 17.05. >> > >> > On Tue, Mar 19, 2019, 3:10 AM Mohammad Nashbat <mnash...@alfaisal.edu> >> > wrote: >> > >> >> Sorry, below is the correct one. >> >> >> >> SELECT * >> >> FROM(SELECT >> >> items.dateaccessioned, >> >> items.barcode, >> >> items.itemcallnumber, >> >> biblio.title, >> >> biblioitems.isbn, >> >> biblioitems.publishercode, >> >> (SELECT >> >> >> ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]')) >> >> AS Subject >> >> FROM items >> >> LEFT JOIN biblioitems ON >> >> (items.biblioitemnumber=biblioitems.biblioitemnumber) >> >> LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) >> >> WHERE items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>> AND >> >> <<and (yyyy-mm-dd)|date>>) AS t WHERE Subject LIKE >> >> concat('%',<<Subject>>,'%') ORDER BY dateaccessioned DESC >> >> >> >> >> >> Thanks & Best Regards, >> >> >> >> Mohammed Nashbat >> >> Library Instructor >> >> >> >> >> >> -----Original Message----- >> >> From: Mohammad Nashbat >> >> Sent: Tuesday, March 19, 2019 10:04 ص >> >> To: 'Craig Butosi'; koha@lists.katipo.co.nz >> >> Subject: RE: [Koha] Koha 18.11 - SQL report to select ISBNs by >> >> Subject >> >> >> >> Hi Craig, >> >> >> >> We are using the below SQL report, I hope it do the needful for you. >> >> >> >> SELECT * >> >> FROM(SELECT >> >> items.dateaccessioned, >> >> items.barcode, >> >> items.itemcallnumber, >> >> biblio.title, >> >> biblioitems.isbn, >> >> biblioitems.publishercode, >> >> (SELECT >> >> >> ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]')) >> >> AS Subject >> >> FROM items >> >> LEFT JOIN biblioitems ON >> >> (items.biblioitemnumber=biblioitems.biblioitemnumber) >> >> LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) >> >> WHERE items.dateaccessioned BETWEEN '2019-02-01' AND '2019-03-19') AS >> >> t WHERE Subject LIKE concat('%','Medicine','%') ORDER BY >> >> dateaccessioned DESC >> >> >> >> >> >> Thanks & Best Regards, >> >> >> >> Mohammed Nashbat >> >> Library Instructor >> >> Alfaisal University >> >> >> >> >> >> -----Original Message----- >> >> From: Koha [mailto:koha-boun...@lists.katipo.co.nz] On Behalf Of >> >> Craig Butosi >> >> Sent: Monday, March 18, 2019 07:29 م >> >> To: koha@lists.katipo.co.nz >> >> Subject: [Koha] Koha 18.11 - SQL report to select ISBNs by Subject >> >> >> >> Hi everyone, >> >> >> >> Running Koha 18.11 on Ubuntu 16.04 w/ MariaDB. Having a bit of >> >> trouble understanding the db structural changes made in 18.xx and how >> >> SQL reports are built in Koha 18.11. I know some tables and such have >> >> changed since the old 16.xx days. >> >> >> >> *I'd like to create a report that draws a list of ISBNs, along with >> >> call number and title, for my coverflow carousel based on subject >> >> (i.e., 650$a) keyword*. I'd like to start featuring subject-based >> >> resources on the carousel, not just new items. >> >> >> >> Any help would be very much appreciated. Just a note: there are a few >> >> reports on the Koha SQL reports wiki that involve querying on the 650 >> >> field; but these do not work for what I would like to achieve. >> >> >> >> Many thanks, >> >> >> >> Craig Butosi, MA, MLIS, B Mus (Hons) >> >> Library: library.craigbutosi.ca >> >> _______________________________________________ >> >> Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz >> >> https://lists.katipo.co.nz/mailman/listinfo/koha >> >> >> >> ________________________________ >> >> >> >> DISCLAIMER: This electronic mail transmission contains confidential >> >> information intended only for the person(s) named. Any use, >> >> distribution, copying or disclosure by any other person is strictly >> >> prohibited. If you received this transmission in error, please notify >> >> the sender by reply e-mail and then destroy the message. Opinions, >> >> conclusions, and other information in this message that do not relate >> >> to the official business of Alfaisal University shall understand to >> >> be neither given nor endorsed by Alfaisal University. The contents of >> >> any attachment to this e-mail may contain software viruses, which >> could damage your own computer system. >> >> While “Alfaisal University” has taken every reasonable precaution to >> >> minimize this risk, we cannot accept liability for any damage which >> >> you sustain as a result of software viruses. You should carry out >> >> your own virus checks before opening the attachment. >> >> _______________________________________________ >> >> Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz >> >> https://lists.katipo.co.nz/mailman/listinfo/koha >> >> >> > >> _______________________________________________ >> Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz >> https://lists.katipo.co.nz/mailman/listinfo/koha >> > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha