[GIS] CartoDB table join error: Column reference is ambiguous

attribute-joinscartosqltable

I've got a CartoDB question that seems dumb b/c the layer is correctly rendering. Anyway, I have two layers, named

  • sampling_points
  • sample_point

I'm trying to inner join the sample_point data to the sampling_points. Basically to render all sampling_points that have their id (suid) in the sample_point table.

Here's my code. 

     SELECT sampling_points.the_geom_webmercator, sampling_points.suid, sample_point.suid
     FROM sampling_points, sample_point
     WHERE sampling_points.suid = sample_point.suid

enter image description here

Best Answer

SELECT sampling_points.the_geom_webmercator, sampling_points.suid, sample_point.suid
 FROM sampling_points, sample_point
 WHERE sampling_points.suid = sample_point.suid

This query is generating two final columns called suid, so the ambiguity is not in the query itself but in the selected results, to sort this out, use alias:

SELECT sampling_points.the_geom_webmercator, 
sampling_points.suid as suidsampling, sample_point.suid as suidsample
FROM sampling_points, sample_point
WHERE sampling_points.suid = sample_point.suid
Related Question