'Creating a view that contains all records from one table, that match the comma separated field content in another table

I have two tables au_postcodes and groups.

  1. Table groups contains a field called PostCodeFootPrint that contains the postcode set making up the footprint.
  2. Table au_postcodes contains a field called poa_code that contains a single postcode.

The records in groups.PostCodeFootPrint look like:

PostCodeFootPrint
2529,2530,2533,2534,2535,2536,2537,2538,2539,2540,2541,2575,2576,2577,2580
2640
3844
2063, 2064, 2065, 2066, 2067, 2068, 2069, 2070, 2071, 2072, 2073, 2074, 2075, 2076, 2077, 2079, 2080, 2081, 2082, 2083, 2119, 2120, 2126, 2158, 2159
2848, 2849, 2850, 2852

Some records have only one postcode, some have multiple separated by a "," or ", " (comma and space).

The records in au_postcode.poa_code look like:

poa_code
2090
2092
2093
829
830
836
2080
2081

Single postcode (always).

The objective is to:

Get all records from au_postcode, where the poa_code appears in groups.*PostCodeFootPrint into a view.

I tried:

SELECT
    au_postcodes.poa_code, 
    groups."NameOfGroup"
FROM
    groups,
    au_postcodes
WHERE
    groups."PostcodeFootprint" LIKE '%au_postcodes.poa_code%'

But no luck

sqlpostgresqlpostgis


Solution 1:[1]

Since the values of PostCodeFootPrint are separated by a common character, you can easily create an array out of it. From there use unnest to convert the array elements to records, and then join then with au_postcode:

SELECT * FROM au_postcode au
JOIN (SELECT trim(unnest(string_to_array(PostCodeFootPrint,','))) 
      FROM groups) fp (PostCodeFootPrint) ON fp.PostCodeFootPrint = au.poa_code;

Demo: db<>fiddle

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jim Jones

Related Questions