SQL for BIB Records Linked to Multiple HOLs or Multiple ADMs

• Article Type: General
• Product: Aleph
• Product Version: 20, 21, 22, 23

Description:
1.  We need an SQL query to find BIB records which have multiple HOL records linked to them.

2.  We need an SQL query to find BIB records which have multiple ADM records linked to them for the same ADM library. (This is an error: there should be only one ADM record per BIB in any one ADM library.)

Resolution:
1a.  This SQL will give you cases of multiple HOLs:
> s+ xxx01
SQL> select substr (z103_rec_key_1,6,9) from z103 where substr (z103_rec_key_1,1,5) = 'XXX01' and Z103_LKR_TYPE = 'HOL' and Z103_LKR_LIBRARY = 'XXX60' group by substr (z103_rec_key_1,6,9) having count(*) > 1;
  <where xxx01 is your bib library and xxx60 is your HOL library> 

1b.  If you want to limit it to cases where the bib/HOL are associated with a particular ADM library, the following will give you that:

> s+ xxx01

SQL> select substr (z103_rec_key_1,6,9) from z103 where substr (z103_rec_key_1,1,5) = 'XXX01' and Z103_LKR_TYPE = 'HOL' and Z103_LKR_LIBRARY = 'XXX60' and substr (z103_rec_key_1,6,9) in (select substr (z103_rec_key_1,6,9) from z103 where substr (z103_rec_key_1,1,5) = 'XXX01' and substr (z103_rec_key,1,5) = 'ZZZ50') group by substr (z103_rec_key_1,6,9) having count(*) > 1; 

  <where xxx01 is your bib library and xxx60 is your HOL library and zzz50 is the particular ADM library>

Note: The preceding SQLs can be run in either the BIB or the HOL library. The result should be the same.

2.  And, for multiple ADM's:
> s+ xxx01
SQL> select substr (z103_rec_key_1,6,9) from z103 where substr (z103_rec_key_1,1,5) = 'XXX01' and Z103_LKR_TYPE = 'ADM' and Z103_LKR_LIBRARY = 'XXX50' group by substr (z103_rec_key_1,6,9) having count(*) > 1;
  <where XXX01 is your bib library and xxx50 is your ADM library>

Note: This SQL can be run in either the BIB or the ADM library. The result should be the same.

Note: The article SQL to locate BIB/HOL recs without items; BIBs without ADM; etc. *MASTER RECORD*  (KB 8192-2744) has a variety of other SQL commands for BIB/ADM/HOL records.