(SQL / PostgreSQL) In a query, how can I translate a field's value into a
more human-readable value using another table as a lookup?
I have postgresql tables with values like:
Table region_data:
region_name | population | region_code
------------+------------+-------------
Region 1 | 120000 | A
Region 2 | 200000 | A
Region 3 | -1 | B
Region 4 | -2 | -1
Where some data may not be available (i.e., the -1 and -2 values)
And tables that contain translations for those values:
Table data_codes:
code | meaning
------+-----------------------
-1 | 'Data not available'
-2 | 'Insufficient data'
...
and
Table region_types:
type | meaning
------+---------------
A | Mountain
B | Grassland
...
I want to make a query (actually a view) that returns the human-readable
translations provided by the data_code and region_types tables. For
instance, the view would return:
Region Name | Population | Region Type
------------+--------------------+-------------
Region 1 | 120000 | Mountain
Region 2 | 200000 | Mountain
Region 3 | Data Not Available | Grassland
Region 4 | Insufficient Data | Data Not Available
I've tried doing some sub-queries, but they return a lot of duplicate rows
where the code doesn't match to anything in the data_code table.
Please help? Thanks!
No comments:
Post a Comment