---
title: Common Queries
slug: redshift/UDcZ-common-queries
icon: {"lucideIcon":"ScanSearch"}
docTags: 
createdAt: 2026-10-01T12:37:29.813Z
---

### Full patient list

```sql
SELECT
    p.id,
    p.nickname,
    pa.number      AS patient_number,
    sp.name        AS species,
    br.name        AS breed,
    p.birthday,
    pa.clinic_id
FROM pet p
JOIN patients pa ON pa.pet_id = p.id
LEFT JOIN species sp ON sp.id = p.species_id
LEFT JOIN breeds br  ON br.id = p.breed_id
WHERE p.deleted_at IS NULL
ORDER BY p.nickname;
```

### Active (non-archived) patient list

Excludes pets that have an archive record — deceased, moved away, changed owner, or other:

```sql
SELECT
    p.id,
    p.nickname,
    pa.number      AS patient_number,
    sp.name        AS species,
    pa.clinic_id
FROM pet p
JOIN patients pa ON pa.pet_id = p.id
LEFT JOIN species sp ON sp.id = p.species_id
WHERE p.deleted_at IS NULL
  AND pa.archived_at IS NULL
  AND NOT EXISTS (
      SELECT 1 FROM vetpetarchive va
      WHERE va.pet_id = p.id AND va.clinic_id = pa.clinic_id
  )
ORDER BY p.nickname;
```

### Deceased patients

```sql
SELECT
    p.id,
    p.nickname,
    va.reason,
    va.created_at AS date_archived
FROM vetpetarchive va
JOIN pet p ON p.id = va.pet_id
WHERE va.archive_type_id = 1
ORDER BY va.created_at DESC;
```