CartoDB: Info window from multiple layers, one geo, one supporting data

Question

I want to have one table (readonly_table) with supporting non-geo data (e.g. project code, name, manager, etc.) and another table (write_table) with geo point, project code, perhaps a few other user entered properties e.g. start date.

And then I want to have these tables joined by project_code as key in a visualization so the info window displays both tables' fields on refresh (assuming readonly_table has a matching project_code).

Example: readonly_table has project codes 1, 2, 3, along with their other field values) The write_table is empty. User goes into the map, clicks "add feature" on the write_table layer. Adds a point. Clicks the "edit data" option. Types in a project code (2). Hits save and close.

Now when the user clicks on the same point on the map, he sees one info window with the merged results of write_table fields (project code, start date) and readonly_table fields (name, manager, etc.)

Is this doable within the CartoDB dashboard? If not, can you show a very basic example of an info window displaying from two tables based on a common field join (and geo data from one of the tables)?

Answer 1

I would see if you can join the tables as documented here :

Say you have some nice polygons for your world borders stored in table_1, but you have a CSV with a value you need for your map and you've uploaded it seperately to table_2. Here is the join command you would run:

So maybe it would look like this:

SELECT write_table.the_geom, write_table.project_code, readonly_table.name, readonly_table.manager  FROM write_table, readonly_table WHERE write_table.project_code = readonly_table.project_code

You can do joins in the UI. I may be wrong, but it seems like that would work in the sql input in the UI. And then you have the results in a visualization with infowindows. I should have tried this before responding. Correct me if wrong.