SQL Is Obnoxious

I find SQL obnoxious, due to its brevity. Getting complicated desired behavior from it sometimes requires clever understanding and combinations of very simple primitives. Because it’s taken me about 2 weeks (off and on) to work it out, I present, with no explanation, this code:

WITH vars (var) AS (
  SELECT UNNEST(string_to_array(ancestry, '/')::integer[]) FROM calibrations AS a
)
SELECT c.id AS cal_id, p.id AS param_id, t.id AS tuning_id, o.* FROM tunings t
  LEFT JOIN LATERAL json_array_elements(t.data::json) WITH ORDINALITY AS o ON TRUE
  JOIN variables v ON v.tuning_id = t.id
  JOIN calibrations c ON v.calibration_id = c.id
  JOIN parameters p ON p.label = t.label
WHERE c.id NOT IN (SELECT var FROM vars)
  AND t.data_type = 'Z_Axis'
  AND p.label = 'C_PME_GainFactor_Table'

Leave a Reply

Your email address will not be published.