Definition

Purpose

The codeable_concept model provides coding concept metadata with terms, alternate codes and SNOMED mappings from the anonymised extract.

Information

This master table contains coded concepts with their canonical and alternative representations. Each concept can map to multiple coding systems including SNOMED and Read v2 codes, enabling terminology translation and concept lookup.

• emis_code_id (PK): The unique identifier for the concept
• snomed_concept_id (FK): Links to the SNOMED concept this code maps to
• snomed_description_id (FK): Links to the SNOMED description

Examples

Get all codeable concepts for an organisation

SELECT *
FROM hive.explorer_recruit_anon.codeable_concept
WHERE organisation = 'EMIS'
LIMIT 100;

Find concepts with a SNOMED mapping

SELECT
  emis_code_id,
  term,
  snomed_concept_id,
  snomed_description_id
FROM hive.explorer_recruit_anon.codeable_concept
WHERE snomed_concept_id IS NOT NULL
LIMIT 100;

Look up a specific EMIS code

SELECT
  emis_code_id,
  term,
  fully_specified_name,
  readv2_code
FROM hive.explorer_recruit_anon.codeable_concept
WHERE emis_code_id = 'A123'
LIMIT 100;