Find Family Members Not Listed as Patients

If you have a cluttered database with many "guarantors" or family members who were entered for billing purposes but never actually received treatment, this report is for you. This open dental family not patients query helps you identify those individuals so you can clean up your records or properly categorize them.

The Query

This query looks for individuals who are listed as a Guarantor for at least one patient but are not themselves marked as an active patient (PatStatus 0) in your system.

/* Query to find Guarantors who are not active patients */
SELECT 
    p.LName, 
    p.FName, 
    p.PatNum, 
    p.HmPhone, 
    p.Email
FROM patient p
WHERE p.PatStatus != 0
AND p.PatNum IN (
    SELECT DISTINCT Guarantor 
    FROM patient 
    WHERE Guarantor != PatNum
)
ORDER BY p.LName, p.FName;

How to Run This Query

1. In Open Dental, go to Reports in the Main Menu.
2. Click User Query.
3. Paste the query provided above into the large text box.
4. Click Submit Query.
5. The results will display in the grid below, showing the names and contact information of the individuals identified.

Understanding the Results

• LName / FName: The last and first name of the person who is currently acting as a guarantor but is not an active patient.
• PatNum: The unique identification number assigned to that person in your Open Dental database.
• HmPhone / Email: Contact details to help you reach out to these individuals if you need to verify their status or update their information.

How to Customize

If you want to narrow your search to only look for people who have never been in the office at all (excluding those marked as "Inactive" or "Archived"), you can modify the WHERE clause.

Change this line:
AND p.PatStatus != 0

To this:
AND p.PatStatus = 5

This will filter the results to only show individuals marked as "Prospective" patients who are currently serving as guarantors for others.

Variations

If you want to see a list of all guarantors regardless of their patient status, simply remove the AND p.PatStatus != 0 line entirely. This is useful if you are performing a database audit to ensure all guarantors have complete contact information, as it will pull every unique guarantor in your system.

Skip the Query — Use DentalCanvas Instead

Don't want to write SQL? DentalCanvas connects to your Open Dental database and shows you this data automatically in a visual dashboard — no queries required.

This article is provided by opendentalsupport.com, an independent community resource. We are not affiliated with Open Dental Software, Inc.