1

I have two tables in CartoDB, one with locations and one with related data to those locations. They are related through the common columns "placeid" and "relatedid". Both columns are string, (and definitely there are common entries).

Still, the query "Select * from lugares, related where lugares.placeid = related.relatedid" gives no results. Where are the possible traps? I managed to make them join correctly when using only small excerpts of the data. Can it be an encoding problem?

Improve this question

2 Answers 2

Reset to default
2

There are going to be a few problems with the query as you have it,

SELECT * FROM lugares, related WHERE lugares.placeid = related.relatedid

is going to return columns with the same name (e.g lugares.the_geom and related.the_geom will both be just, the_geom). This is an issue that will make a lot of things not work right. Try a sanity check version,

SELECT ug.cartodb_id FROM lugares ug, related re WHERE ug.placeid = re.relatedid LIMIT 1

From that, you should get back a single row, with a single column, cartodb_id. IF you don't get anything, my guess is that your data do not in fact match up. If it does work, start adding columns explicitly by tablename.name, doing the '*' shorthand isn't going to work well for joins in CartoDB due to shared column names. So in the end, your query might be,

SELECT ug.cartodb_id, ug.the_geom, ug.the_geom_webmercator, re.relatedid 
FROM lugares ug, related re WHERE ug.placeid = re.relatedid

If you are running this via CartoDB.js or through the API, double check the error, perhaps one of your tables is set to private.

Improve this answer
1
  • Hm, it's still behaving weird. I actually did also try other columns instead of *. Without where clause, doing "SELECT related.cartodb_id FROM lugares, related", it works and I get a huge result. When selecting "lugares.cartodb_id" instead, I get only one row with the cartodb_id 1. Again, adding "WHERE related.relatedid = lugares.placeid" to the first query, no rows as result. The column values do match up, I ran the same query on the same tables in MS Access without problem, getting the results I wanted. And no table is set to private, I have a public account.
    – user2270611
    Commented Dec 23, 2013 at 8:42
1

Though I have no real answer to my initial problem, I solved the issue by replacing the common string-field with a numeric field, now the join works. Even though the common columns did not use special characters, I still suspect it's got something to do with encoding, because in other columns Spanish special characters are messed up in my table.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.