# Finding Bibs with ONLY Lost and Missing Items

**URL:** https://forum.innovativeusers.org/t/finding-bibs-with-only-lost-and-missing-items/2632
**Category:** Polaris Repository
**Created:** [September 22, 2025, 8:19pm UTC](https://forum.innovativeusers.org/t/finding-bibs-with-only-lost-and-missing-items/2632 "2025-09-22T20:19:27Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![wesochuck](https://forum.innovativeusers.org/user_avatar/forum.innovativeusers.org/wesochuck/32/499_2.png) [@wesochuck](https://forum.innovativeusers.org/u/wesochuck)
#### Post date: [September 22, 2025, 8:19pm UTC](https://forum.innovativeusers.org/t/finding-bibs-with-only-lost-and-missing-items/2632/1 "2025-09-22T20:19:27Z")

</div>

This query identifies bibliographic records that meet two specific conditions:

1. **They have at least one item record.** This ensures you are looking at items that are part of your active collection, not titles where all items might have been deleted.

2. **All of their active item records are either “Lost” or “Missing.”** This is the key part of the query. It uses a **NOT EXISTS** clause to filter out any bibliographic record that has an active item with a status other than “Lost” or “Missing”.

#### How the Script Works

The script uses a temporary table variable, `@StatusList`, to hold the item statuses you want to check for. The query then uses two `EXISTS` clauses to filter the bibliographic records.

```sql
-- Declare a TABLE variable to hold the status descriptions you want to check.
DECLARE @StatusList TABLE (
    Description NVARCHAR(100)
);

-- Populate the variable with the descriptions from the polaris.polaris.ItemStatuses table
-- You can easily add more here, like 'On-Order' or 'In-Repair'.
INSERT INTO @StatusList (Description)
VALUES ('Lost'), ('Missing');

-- Main query
SELECT
    br.BibliographicRecordID,
    br.BrowseTitle
FROM
    polaris.polaris.BibliographicRecords AS br
WHERE
    -- Check that at least one ACTIVE item exists for the bib.
    EXISTS (
        SELECT 1
        FROM polaris.polaris.CircItemRecords cir_check
        WHERE cir_check.AssociatedBibRecordID = br.BibliographicRecordID
          AND cir_check.RecordStatusID = 1 -- make sure all item records are final status
    )
    -- Now, check that NO ACTIVE items exist that are NOT in our @StatusList.
    AND NOT EXISTS (
        SELECT 1
        FROM polaris.polaris.CircItemRecords AS cir
        INNER JOIN polaris.polaris.ItemStatuses AS istatus
            ON cir.ItemStatusID = istatus.ItemStatusID
        WHERE
            cir.AssociatedBibRecordID = br.BibliographicRecordID
            AND cir.RecordStatusID = 1 -- make sure all item records are final status
            AND istatus.Description NOT IN (SELECT Description FROM @StatusList)
    );

```

####