(SQL / PostgreSQL) In a query, how can I translate a field value into a more human-readable value using another table as a search?

Question

I have postgresql tables with values, for example:

Table region_data: region_name | population | region_code ------------+------------+------------- Region 1 | 120000 | A Region 2 | 200000 | A Region 3 | -1 | B Region 4 | -2 | -1 

If some data may not be available (i.e. -1 and -2)

And tables containing translations for these 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 example, the view will 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 tried to do some subqueries, but they return many duplicate rows where the code does not match any in the data_code table.

Please, help? Thanks!

Answer 1

Assuming there is no conflict between the data codes and the regional codes, I see two problems. One of them is the data type problem in the population column (the value is an integer, but a string is required for the data value). Another combines region codes with data codes:

 select rd.region_name, (case when population >= 0 cast(population as varchar(255)) else p.meaning end) as population, r.meaning from region_data rd left outer join (select type, meaning from region_types union all select code, meaning from data_codes ) r on rd.region_code = r.type left outer join data_codes p on rd.population < 0 and rd.population = p.code; 

Answer 2

 select r.region_name, coalesce(d1.meaning, r.population::text) as population, coalesce(d2.meaning, rt.meaning, r.region_code) as region_code from region_data as r left outer join data_codes as d1 on d1.code = r.population left outer join data_codes as d2 on d2.code::text = r.region_code left outer join region_types as rt on rt.type = r.region_code order by r.region_name 

=> sql script demonstration

Answer 3

You may be able to post a request. duplicate rows when using joins usually mean INNER JOIN instead of LEFT JOIN