Join in CARTO - Relate tables 1:M

Question

I'm trying to make a join between two tables in SQL with 1: M relation. A table ejecutado_unificado_2018 is a dataset without geographic information and the dataset sectores_limpieza_2018 comes from a shapefile. When I do the join, I do not recognize the cartodb_id or the geometry when I use the_geom_webmercator. I would like to know my mistake.

SELECT sectores_limpieza_def2.the_geom AS the_geom_webmercator,
       sectores_limpieza_def2.cartodb_id
FROM ejecutado_unificado_2018
JOIN sectores_limpieza_def2
  ON sectores_limpieza_def2.codigo=ejecutado_unificado_2018.codigo

Answer 1

CARTO is going to return errors if any of the mandatory fields -cartodb_id, the_geom and the_geom_webmercator are not present in the SELECT. So in order to make your query work, you need to add them:

SELECT 
  b.the_geom,
  b.the_geom_webmercator,
  a.cartodb_id
FROM 
  ejecutado_unificado_2018 a
JOIN 
  sectores_limpieza_def2 b ON a.codigo = b.codigo

Also, notice that there were also two key mistakes. On the one hand, you were selecting the cartodb_id from the geometry table. As far I understood, you wanted to keep every row from your non-geometry table. So for your use case, the proper way to do it is adding the id of this former table (and any thematic fields you need). And on the other, you were renaming the_geom to the_geom_webmercator without any transformation. For future reference, you should use ST_Transform.

Finally, notice that there is a join analysis available in BUILDER UI called Add columns from second table. If using this, you will not need to apply SQL.