Finding items linked to more than one bib record in Sierra

Sierra, Millennium & Encore
1

Does anyone have experience of doing this? The documentation on the Supportal says:

Currently there is no way to issue a command to find bibliographic records with more than one linked record of a given type. The ^ LINKED REC command allows the user to search for the existence or non-existence of a link to a given record type, but there is no way to specify quantity of the record type. Some libraries output bibliographic record numbers and attached record numbers from the database and use a spreadsheet or other text-processing utility to reduce the list to just those records with multiple records attached.

I’ve exported a sample file that I know includes some items linked to more than one bib, but I can’t see how to manipulate it to get the desired result.

2

SQL is the best answer to this.

If you cannot use SQL, you can use Create Lists and export the bib record number and the item record number for all bibs where a link to item exists. In Excel, add the following formula in Column C, row 2, to count the attached items (Column A is the bib number and Column B is a string of the items): =LEN(B2)-LEN(SUBSTITUTE(B2,“;”,“”))+1

If you can use SQL, I stripped down one of Jeremy Goldstein’s queries, below. (There’s probably a simpler query for this and if you don’t want the title, you can definitely strip it down further.) I had to add a line at the beginning to set a longer timeout, as our connection is slow:

SET statement_timeout TO '5min';

SELECT 
id2reckey(b.bib_record_id)||'a' as "record_num",
b.best_title,
count(item.id) as "item_count"
from 
sierra_view.bib_record_property as b
JOIN
sierra_view.bib_record bib
ON
b.bib_record_id = bib.id
join
sierra_view.bib_record_item_record_link as link
on
b.bib_record_id = link.bib_record_id
join
sierra_view.item_view as item
on
item.id = link.item_record_id

group by 1, 2
having count(item.id) > 1
order BY 2 DESC
;
2 Likes
3

Thank you Sarah for your detailed reply. Our IT officer has run the SQL query, which produces a very useful listing of how many items are attached to each bib. record, but unfortunately, this doesn’t answer my query. Looking back at my original post, I can see that I quoted the wrong paragraph from the relevant page in the Supportal, so please accept my apologies for misleading you.

This is what I should have quoted:

How can we find all items with additional links to bibliographic records?

The Linking items to additional bibs function allows staff to link a single item to multiple bibliographic records to reflect that multiple physical pieces are bound together. Create Lists does not have special logic to let the user search for an item that is attached to more than one bibliographic record. There is no system function that searches specifically for the existence of multiple links (also known as “XLINK”) in the bibliographic and item records. You can sort a list of item records by their bibliographic record number, or vice versa, and Output, or List, or Export the record numbers and sort and parse in a spreadsheet or other text-processing utility, in order to find your desired records. Users should plan to locate items that are linked to multiple bibliographic records through other information in the bibliographic or item record that identifies these “bound-withs”, such as a fixed field.

So what we are looking for is items made up of separately published manifestations bound together. Each manifestation has its own bib. record, and for this reason the items are linked to more than one bib. record

4

Oh, I see. Give this one a try:

SET statement_timeout TO '5min'; 
SELECT 
id2reckey(item.id)||'a' as "record_num",
count(bib.id) as "bib_count"
FROM 
sierra_view.item_view as item
join
sierra_view.bib_record_item_record_link as link
on
item.id = link.item_record_id
JOIN
sierra_view.bib_record bib
ON
bib.id = link.bib_record_id
group by 1
having count(bib.id) > 1
;
1 Like
5

Thank you so much Sarah. This worked really well. I’ve also been able to run a list of the associated bib. records on Sierra off the list created by your query. It’s very useful to be able to compare the two. Thank you again.

1 Like