Search surname + place / county

Custom SQL query returning 100 rows (hide)

SELECT n.surname, n.forename, n.occupation,
       p.locality, p.county,
       c.county AS venue, c.case_type, c.year, c.term,
       c.side, c.image, c.roll_label, c.roll_number,
       r.aalt_roll_url, c.source_downloaded_at,
       n.role, n.raw_segment
FROM names n
LEFT JOIN places p ON p.id = n.place_id
JOIN cases c ON c.id = n.case_id
LEFT JOIN rolls r ON r.id = c.roll_id
WHERE n.surname LIKE (
    CASE
      WHEN INSTR(:surname, '%') > 0 OR INSTR(:surname, '_') > 0
      THEN :surname
      ELSE :surname || '%'
    END
  )
  AND (NULLIF(:role, '') IS NULL OR n.role = :role)
  AND (
    NULLIF(:place, '') IS NULL
    OR p.locality LIKE (
      CASE
        WHEN INSTR(:place, '%') > 0 OR INSTR(:place, '_') > 0
        THEN :place
        ELSE :place || '%'
      END
    )
    OR p.place_name LIKE (
      CASE
        WHEN INSTR(:place, '%') > 0 OR INSTR(:place, '_') > 0
        THEN :place
        ELSE :place || '%'
      END
    )
  )
  AND (
    NULLIF(:county, '') IS NULL
    OR p.county LIKE (
      CASE
        WHEN INSTR(:county, '%') > 0 OR INSTR(:county, '_') > 0
        THEN :county
        ELSE :county || '%'
      END
    )
  )
  AND (
    NULLIF(:venue, '') IS NULL
    OR c.county LIKE (
      CASE
        WHEN INSTR(:venue, '%') > 0 OR INSTR(:venue, '_') > 0
        THEN :venue
        ELSE :venue || '%'
      END
    )
  )
  AND c.year IS NOT NULL
  AND c.year >= CAST(COALESCE(NULLIF(:start_year, ''), '1349') AS INTEGER)
  AND c.year <= CAST(COALESCE(NULLIF(:end_year, ''), '1596') AS INTEGER)
ORDER BY c.year, n.surname
LIMIT 100

Query parameters

surname

place

county

venue

role

start_year

end_year

Edit SQL