[Koha] Help with SQL script please

[Alvaro Cornejo]

Hi Gustavo

Not sure where the error might be but for troubleshooting you can split
your query from the inside out and see where it stops working. Also, if you
do know the values for Jesuit%, you might replace that query with a list of
values or a specific value for testing purposes.

A) Test
         SELECT authid
         FROM auth_header
         WHERE ExtractValue(marcxml,
'//datafield[@tag="373"]/subfield[@code="a"]') LIKE "Jesuit%")

B)
     SELECT biblionumber
     FROM biblio_metadata
     WHERE ExtractValue(metadata,
'//datafield[tag=100]/subfield[@code="9"]') IN
         (a list of known values or the results of A)

C)

SELECT i.itemnumber, b.title, b.author
FROM items i
LEFT JOIN biblio b ON b.biblionumber = i.biblionumber
WHERE i.biblionumber IN (list)
     (you can use the values got in B)

Hope helps

Alvaro


|----------------------------------------------------------------------------------------|
 Stay safe / Cuídate/  Reste sécurisé
*7* Switch off as you go / Apaga lo que no usas /  Débranchez au fur et à
mesure.
 *q *Recycle always / Recicla siempre / Recyclez toujours
 P Print only if absolutely necessary / Imprime solo si es necesario /
Imprimez seulement si nécessaire


Le lun. 24 août 2020 à 19:29, Carlos Lopez <clopez at dml.vic.edu.au> a écrit :

> 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
>