Common Queries

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;

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;

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;