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
