left join returning one more row than expected

Question

I have a table of single polygons (table rast_poly) derived from a raster and a table of polygons that represents buffered points (table pt_buff). The raster polygon geometry has been reduced by using an ST_Intersection() operation with the buffered points, so the geom is clipped to those boundaries. Each polygon from the raster contains an integer value that corresponds to a crop code. I want to sum the areas for each crop type within each buffered point polygon. I have to join the cropcodes table to the first query on 'value' and 'country' because the value-category pairs are unique to each country. I am almost there with the following code:

select t3.id, t3.country, t3.val, t3.km2::numeric(10,2), t4.category
from (select t2.id, t1.val, sum(st_area(t1.geom::geography)/1000000) as km2, t1.country
      from rast_poly t1, pt_buff t2
      where st_intersects(t1.geom, t2.geom) and t2.id = '17DYR07' 
      group by t2.id, val, t1.country
      ) t3
left join cropcodes t4 on t3.val = t4.value and t3.country = t4.country;

Here is the output:

I've checked the results against both the dumped polygons from the raster, and the original raster itself. Everything is correct except the two duplicate rows at the bottom of the output.

How can I fix this?

Answer 1

The inner query (t3) seems fine as the result is grouped by value/country. You can run it alone and ensure there is no duplicate.

The output table shows complete records for the duplicates, meaning there is a data row in both t3 and t4. The probable issue is therefore that there are duplicates in t4, which could be identified by

select value, country, count(*)
from cropcodes 
group by value, country
having count(*) > 1;