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