[Koha] Help with SQL script please
Carlos Lopez
clopez at dml.vic.edu.au
Wed Aug 26 13:11:01 NZST 2020
Hi all
I sent this through yesterday (see below) and it's really annoying me: The query seems to bring up 0 results when I'm trying to compare authids (UNSIGNED) to an ExtractValue string from biblio_metadata.metadata (LONGTEXT?). Is this even possible? If so, how do I go about CONVERTing my authids so that they're the same data type as the ExtractValue strings?
Or am I doing this the wrong way? Is there a better way for me to list a set of itemnumbers from a known set of authids?
With kind regards from the Dalton McCaughey Library Team
Carlos Lopez
Dalton McCaughey Library | 29 College Crescent, Parkville, VICTORIA 3052
Ph: 03 9340 8888 ext.1 | library at dml.vic.edu.au<mailto:library at dml.vic.edu.au> | library.dmlibrary.org.au
________________________________
From: Koha <koha-bounces at lists.katipo.co.nz> on behalf of Carlos Lopez <clopez at dml.vic.edu.au>
Sent: Tuesday, 25 August 2020 10:28 AM
To: koha <koha at lists.katipo.co.nz>
Subject: [Koha] Help with SQL script please
Hi everyone
I'm trying to produce a list of itemnumbers for a list of bibliographic records with biblionumbers where subfield $9 in the 100 tag is in a list of authids for authorities with the value "Jesuit" in 373$a (where trying to populate a particular ccode with a set of items that we know will be relevant for that ccode).
I have the following SQL query which SEEMS to run but brings up no results (even though I know that we have authority records with 373$aJesuit linked from bibliographic records with items attached):
----
SELECT i.itemnumber, b.title, b.author
FROM items i
LEFT JOIN biblio b ON b.biblionumber = i.biblionumber
WHERE i.biblionumber IN
(SELECT biblionumber
FROM biblio_metadata
WHERE ExtractValue(metadata, '//datafield[tag=100]/subfield[@code="9"]') IN
(SELECT authid
FROM auth_header
WHERE ExtractValue(marcxml, '//datafield[@tag="373"]/subfield[@code="a"]') LIKE "Jesuit%"))
----
Can anyone spot where I've gone wrong? Or perhaps point me to an easier way of doing this?
With kind regards from the Dalton McCaughey Library Team
Carlos Lopez
Dalton McCaughey Library | 29 College Crescent, Parkville, VICTORIA 3052
Ph: 03 9340 8888 ext.1 | library at dml.vic.edu.au<mailto:library at dml.vic.edu.au> | library.dmlibrary.org.au
_______________________________________________
Koha mailing list http://koha-community.org
Koha at lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
More information about the Koha
mailing list