SQL to Query Duplicate Titles from different locations

In an Innovative Interfaces OPAC, here is discussion on getting duplicates:

Aim to get duplicate titles from one specific record load rather than all duplicates in the database, I begin with a nested subquery that limits the selection to bib records that were created on a specific date. You will need to change the date to the date of your problem record load.

Since it is E-books that you are concerned about, I have limited the selection based on material type code (Bcode2). You'll need to change that to a correct E-book code for your system.

Rather than limit by item location code, I chose to limit by bib location. You'll need to change that too.

I included a match on ISBN. Where there are multiple ISBNs in a single record, there will be multiple references in the output, one for each Title/ISBN pair. I have been warned in the past that the phrase_entry view may not persist or may change. There are other ways of getting to the ISBN which may be safer in the long run.

-- =========================================================================

-- Duplicate E-book titles from specified load date

-- Brent Searle. Langara College. 2015-03-04

-- =========================================================================

SELECT

   bib.title                                     AS "Title",

   phrase.index_entry                            AS "ISBN",

   COUNT(*)                                      AS "Count"

FROM

   sierra_view.bib_view AS bib

JOIN

   sierra_view.bib_record_location               AS bibloc

ON

   bibloc.bib_record_id = bib.id

JOIN

   sierra_view.phrase_entry                      AS phrase

ON

   phrase.record_id = bib.id

WHERE

   bib.title IN

   (

     SELECT

       title

     FROM

       sierra_view.bib_view

     WHERE

       DATE(record_creation_date_gmt) = '2015-01-23'-- Date of E-book load

   )

   AND

   bib.bcode2 = 'd'                                -- E-book

   AND

   bibloc.location_code = 'le'                     -- Langara Electronic Resource

   AND

   phrase.index_tag = 'i'                          -- Standard Number Index

GROUP BY 1,2

HAVING COUNT(*) > 1                               -- Include only duplicates

ORDER BY 1,2

;

This may be not at all what you were looking for but I hope it helps some.

--

Brent Searle