Duplicate entries

Polaris
1

Hi all. In Sierra, Headings Reports I used to run a search for “Duplicate Entries”. I’m not sure what all it was searching but I’d get a list of duplicate patron and item barcodes and bibs that had the same 001 tag (I believe). What the best way to find these in Polaris? Thanks.

2

Hello Colleen,

Duplicate patron barcodes:

select barcode from circitemrecords with (nolock)
where recordstatusid=1
group by barcode
having count(*)>1

Duplicate item barcodes is the same but the table is ‘circitemrecords’ instead of patrons. I got more results when I stopped excluding deleted items, but IDK how much you care about those cases.

The 001 field is just the control number for the bib record so I don’t believe Polaris will let there be duplicates.

3

Hi @ebrondermajor . Thanks for this. Is “circitemrecords” the table for items? What is the table for patron barcodes? Sorry if I’m misreading. Thanks.

4

Oh yeah, my bad lol, used the wrong table. It should be like this:

Patrons:

select barcode from patrons with (nolock)
where recordstatusid=1
group by barcode
having count(*)>1

Items:

select barcode from circitemrecords with (nolock)
where recordstatusid=1
group by barcode
having count(*)>1
5

Thanks, Eleanor. That did find me some duplicates.

6

Hi @ebrondermajor. I have another question about the duplicate patron query below that gave me. Is there a way to either do this as an SQL search in Leap or if not, then have the output be patronID instead of barcode? Thanks.

select barcode from patrons with (nolock)
where recordstatusid=1
group by barcode
having count(*)>1

7

To use SQL in the Find Tool, the only column output from the SQL must be the primary key for the record type you’re searching* (patron = patronid, item = itemrecordid, etc.). There is some information in the documentation, the Staff Client documentation is better than Leap’s, but it works exactly the same.

Things get tricky in SQL when you want to group by one thing and then output another thing, so I don’t think you can simply change the one field in @ebrondermajor’s original code. But here is a suggestion that might work.

SELECT patronid
FROM   polaris.polaris.patrons WITH (nolock)
WHERE  recordstatusid = 1
       AND barcode IN (SELECT barcode
                       FROM   polaris.polaris.patrons WITH (nolock)
                       WHERE  recordstatusid = 1
                       GROUP  BY barcode
                       HAVING Count(barcode) > 1)

*Note: Technically speaking, the system will accept ANY SQL that outputs a single column of integers. The system doesn’t check what tables you’re selecting FROM, so if your select statement outputs integers, it will try to find matching records.

An analogy (from Gemini): Think of the “Find Tool for Patrons” as a room full of doors, where behind each door is a patron record. Your SQL query is a machine that makes keys. To open the proper doors in this room, your machine must produce patron-shaped keys (patronid). If you accidentally build your query to produce item-shaped keys (itemrecordid), some of the keys might match, when you have a matching ID number in the patron and item database, for example they both have a record id of 1234, but when you unlock the patron door, you won’t find the patron record you were expecting behind the door.

Moral of the story, always make sure to check your Find Tools results and make sure you’re in the proper Find Tool access point for the area you’re querying in your select statement.

1 Like
8

This looks good to me! :slight_smile: Thanks Wes!

9

Looks good! The results outputting barcodes was 12 and outputting patronid was 24. Since it was a small number of barcodes I looked each of them up and they all linked to 2 different patrons so 24 is correct. Thanks so much!

1 Like