[Koha] Koha 18.11 - SQL report to select ISBNs by Subject

[Mohammad Nashbat]

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 at 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-bounces at lists.katipo.co.nz] On Behalf Of Craig Butosi
Sent: Monday, March 18, 2019 07:29 م
To: koha at 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 at 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.