[Koha] SQL Report - Authorities. List duplicate values based on 010$a

[Stefano Bargioni]

Maybe a self join can help you.
See https://www.w3schools.com/sql/sql_join_self.asp <https://www.w3schools.com/sql/sql_join_self.asp> 
However, your query could be very slow, since the match will not work on indexed data. If not sure, but try

SELECT A.authid AS authid1, B.authid AS authid2, ExtractValue(A.marcxml,'/record/datafield[@tag="010"]/subfield[@code="a"]') AS dup
FROM auth_header A, auth_header B
WHERE A.authid <> B.authid
AND ExtractValue(A.marcxml,'/record/datafield[@tag="010"]/subfield[@code="a"]') <> ''
AND ExtractValue(A.marcxml,'/record/datafield[@tag="010"]/subfield[@code="a"]') = ExtractValue(B.marcxml,'/record/datafield[@tag="010"]/subfield[@code="a"]')
ORDER BY A.authid;

HTH. Stefano

> Subject: [Koha] SQL Report - Authorities. List duplicate values based
> 	on	010$a
> Message-ID:
> 	<CAP4tGF9Em5-4xiMHerf9B6sS+9ogzmh8_iMgKz-zJdtDJeBh6A at mail.gmail.com>
> Content-Type: text/plain; charset="UTF-8"
> 
> Hi all,
> 
> Koha 18.11 on Ubunti 16.04
> 
> May I request help for an SQL report that does the following, please:
> 
> Identify and list by authid and main heading all authority records that
> have a tag 010$a with duplicate values?
> 
> I am attempting to located all authority records that can be merged (or
> de-duped via merge).
> 
> Many thanks!
> 
> Craig Butosi, MA, MLIS, B Mus (Hons)
> Library: library.craigbutosi.ca
> 
> 
> ------------------------------
> 
> Message: 2
> Date: Sun, 12 May 2019 01:49:55 +0530
> From: vijay kumar <vijcrjbhu at gmail.com>
> To: koha at lists.katipo.co.nz
> Subject: [Koha] upgrade from koha-17.05.06 to koha-latest
> Message-ID:
> 	<CACNt1H6pKRZsRJiQQSeoevwDbuMYRwDmxCOiMBksweSfPUbGCg at mail.gmail.com>
> Content-Type: text/plain; charset="UTF-8"
> 
> Dear all,
> We have koha-17.05.06, installed using tarball method.
> 
> I have followed the following methods for upgrading from koha-17.05.06 to
> koha-latest
> 1. Updated koha-list source accordingly koha-stable
> 2. Update all perl dependencies and cpan module
> 
> vijay at debian:/usr/share/koha-18.11.04$ perl Makefile.PL
> /usr/share/koha-17.05.06/misc/koha-install-log
> /usr/share/koha/misc/koha-install-log
> 
> and also
> 
> vijay at debian:/usr/share/koha-18.11.04$ sudo perl Makefile.PL
> /usr/share/koha-17.05.06/misc/koha-install-log
> /usr/share/koha-18.11.04/misc/koha-install-log
> 
> vijay at debian:/usr/share/koha-18.11.04$ sudo make
> 
> vijay at debian:/usr/share/koha-18.11.04$ sudo make upgrade
> ------------------------------------------------------------------------------------
> 
> Getting the following error during upgrade
> Kindly suggest the proper method for up-gradation of koha-latest
> 
> With regards,
> Vijay Kumar
> 
> 
> ::::error::::
> 
> vijay at debian:/usr/share/koha-18.11.04$ sudo make upgrade
> Can't locate C4/Installer/UpgradeBackup.pm in @INC (you may need to install
> the C4::Installer::UpgradeBackup module) (@INC contains: /etc/perl
> /usr/local/lib/x86_64-linux-gnu/perl/5.24.1 /usr/local/share/perl/5.24.1
> /usr/lib/x86_64-linux-gnu/perl5/5.24 /usr/share/perl5
> /usr/lib/x86_64-linux-gnu/perl/5.24 /usr/share/perl/5.24
> /usr/local/lib/site_perl /usr/lib/x86_64-linux-gnu/perl-base).
> BEGIN failed--compilation aborted.
> Makefile:22908: recipe for target 'make_upgrade_backup' failed
> make: *** [make_upgrade_backup] Error 2
> 
> 
> ------------------------------
> 
> Message: 3
> Date: Sun, 12 May 2019 08:27:33 +1200
> From: David Nind <david.nind at gmail.com>
> To: Craig Butosi <cbutosi at gmail.com>
> Cc: koha <koha at lists.katipo.co.nz>
> Subject: Re: [Koha] SQL Report - Authorities. List duplicate values
> 	based on	010$a
> Message-ID:
> 	<CAK5OMPcJCLGVyOHFBubZ2-Et9-Tf-cTWyk+Ui21eYPXhxE-z6g at mail.gmail.com>
> Content-Type: text/plain; charset="UTF-8"
> 
> Hi Craig.
> 
> I'm not that great at reports, but you might be able to find something in
> the SQL reports library that could help:
> https://wiki.koha-community.org/wiki/SQL_Reports_Library
> 
> 
> David Nind | david.nind at gmail.com
> PO Box 12367, Thorndon, Wellington, New Zealand 6144
> m. +64 21 0537 847
> 
> 
> On Sun, 12 May 2019 at 05:46, Craig Butosi <cbutosi at gmail.com> wrote:
> 
>> Hi all,
>> 
>> Koha 18.11 on Ubunti 16.04
>> 
>> May I request help for an SQL report that does the following, please:
>> 
>> Identify and list by authid and main heading all authority records that
>> have a tag 010$a with duplicate values?
>> 
>> I am attempting to located all authority records that can be merged (or
>> de-duped via merge).
>> 
>> 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
>> 
> 
> 
> ------------------------------
> 
> Subject: Digest Footer
> 
> _______________________________________________
> Koha mailing list
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
> 
> 
> ------------------------------
> 
> End of Koha Digest, Vol 163, Issue 12
> *************************************