Skip to content

Instantly share code, notes, and snippets.

@yvanzo
Created July 25, 2020 21:28
Show Gist options
  • Save yvanzo/d8d2d4b44e56bb53da38150b8b6838c6 to your computer and use it in GitHub Desktop.
Save yvanzo/d8d2d4b44e56bb53da38150b8b6838c6 to your computer and use it in GitHub Desktop.
Get country code for more than one area
WITH RECURSIVE area_descendants AS (
SELECT entity0 AS parent, entity1 AS descendant, 1 AS depth
FROM l_area_area laa
JOIN link ON laa.link = link.id
WHERE link.link_type = 356
AND entity1 IN (5099, 8127)
UNION
SELECT entity0 AS parent, descendant, (depth + 1) AS depth
FROM l_area_area laa
JOIN link ON laa.link = link.id
JOIN area_descendants ON area_descendants.parent = laa.entity1
WHERE link.link_type = 356
AND entity0 != descendant
)
SELECT ad.descendant, iso.code
FROM area_descendants ad
JOIN iso_3166_1 iso ON iso.area = ad.parent;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment