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

[Barton Chittenden]

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 <MNashbat at 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 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.
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>