[Koha-devel] How can I improve this report?

[Tomas Cohen Arazi]

I think you can improve it by doing a LEFT JOIN between the two tables
instead of the IN comparison.

That way you will find those without matching authorities when they
auth_header counterpart is NULL.

If you want to go precise, you should also add 100$9 vs authid to the game.

Good luck!

El sáb, 2 jul 2022 10:51, Doug Kingston <dpk at randomnotes.org> escribió:

> Report: Authors not in Authorities
>
> SELECT DISTINCT(author) AS heading FROM biblio WHERE author NOT IN (SELECT
> ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code="a"]') AS
> heading FROM auth_header WHERE authtypecode='PERSO_NAME')ORDER BY heading
>
> I am not a SQL expert, but I believe this is doing an unkeyed troll
> through every authority record for every biblio (O(Nbibs * Mauths), which
> is pretty bad).  I suspect we can do better by trolling Auths once to
> create a set and then compare Bib authors against it.  How can we do this?
>
> -Doug-
> _______________________________________________
> Koha-devel mailing list
> Koha-devel at lists.koha-community.org
> https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel
> website : https://www.koha-community.org/
> git : https://git.koha-community.org/
> bugs : https://bugs.koha-community.org/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.koha-community.org/pipermail/koha-devel/attachments/20220702/4a61f388/attachment.htm>