Skip to main content

Record Retrieval with Join

Context​

Retrieve data across tables, join on a common identified-identifier

1. Tokenise identifier value​

SELECT
  DATA_REQUESTS.SINGLETON.<table_a>_LOCK(:raw),
  DATA_REQUESTS.SINGLETON.<table_b>_LOCK(:raw)

2. Join and Filter with tokenised value​

SELECT a.<identifier>, b.<identifier>, ...
FROM <table_a> a
  JOIN <table_b> b ON a.<identifier> = b.<identifier>
WHERE
  a._LOCK_<identifier> = :tokenised_a
  AND b._LOCK_<identifier> = :tokenised_b
ORDER BY a.REFERENCE_PERIOD DESC
LIMIT 1
  • filter by the _LOCK_<identifier>, and project the <identifier> column.
warning

The following query will NOT be optimised due to usage of non-static value in query

SELECT a.<identifier>, b.<identifier>, ...
FROM <table_a> a
  JOIN <table_b> b ON a.<identifier> = b.<identifier>
WHERE
  a._LOCK_<identifier> IN (SELECT DATA_REQUESTS.SINGLETON.<table_a>_LOCK(:raw))
  AND b._LOCK_<identifier> IN (SELECT DATA_REQUESTS.SINGLETON.<table_b>_LOCK(:raw))
ORDER BY a.REFERENCE_PERIOD DESC
LIMIT 1

The following query will NOT WORK due to differing tokenised values for different tables

SELECT a.<identifier>, b.<identifier>, ...
FROM <table_a> a
  JOIN <table_b> b ON a._LOCK_<identifier> = b._LOCK_<identifier>
WHERE
  a._LOCK_<identifier> = :tokenised_a
  AND b._LOCK_<identifier> = :tokenised_b
ORDER BY a.REFERENCE_PERIOD, b.REFERENCE_PERIOD DESC
LIMIT 1

Example - obtaining CPF contribution and HDB housing grant data for a given UIN​

note

Example Data Request ID: GOVTECH-2025-01-001

1. Tokenise UIN values
SELECT
  DATA_REQUESTS.SINGLETON.V_DH_CPFB_WAGECON_LOCK(:uin),
  DATA_REQUESTS.SINGLETON.V_DH_HDB_GRANTS_LOCK(:uin)
2. Join and Filter with tokenised UIN
SELECT
  a.UIN,
  a.CPF_SUBMISSION_NO,
  a.RELEVANT_CONTRIBUTION_MONTH,
  a.NET_CONTRIBUTION_AMT,
  a.DECLARED_ORDINARY_WAGE_AMT,
  b.GRANT_AMOUNT,
  b.TYPE_OF_HOUSING_GRANT,
  a.RELEVANT_CONTRIBUTION_MONTH,
  b.REFERENCE_PERIOD
FROM
  DATA_REQUESTS.GOVTECH_2025_01_001.V_DH_CPFB_WAGECON a
    JOIN DATA_REQUESTS.GOVTECH_2025_01_001.V_DH_HDB_GRANTS b ON a.UIN = b.UIN
WHERE
  a._LOCK_UIN = :tokenised_a
  AND b._LOCK_UIN = :tokenised_b
ORDER BY
  a.RELEVANT_CONTRIBUTION_MONTH, b.REFERENCE_PERIOD DESC
LIMIT 1