[Koha] Help with SQL script please

[Carlos Lopez]

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