Many to one join in Carto

Question

I have two datasets:

1. A point shapefile of companies (each row/record is a unique company)
2. A list of awards given to those companies (each row is a unique award, but since companies can win multiple awards, the same company can exist in multiple rows).

I want to create a Carto map where each point on the map represents one company, but when you click on that company point's pop-up, it shows all the awards that that company has won.

I've tried multiple variations of the "Link second layer" and "Add Columns from Second Dataset" analysis options, but I'm not getting the result I want. I'm thinking that the join created in "Add Columns from Second Dataset" is just a one-to-one join, and it's only joining on the first record it comes across that has a shared company, OR the pop-up itself is restricted to showing only one award.

Answer 1

Builder's Add Columns from Second Dataset is the analysis you are looking for. It will join one to many. So my guess is that you are dealing with the classic overlapping point problem. As you have commented, you would have several geometries one on top of the other. When clicking on a point, it will show you the information of the last element to be rendered. In order to fix this problem there are several (not so easy) workarounds. In this help article, you will find links to some of them.

Answer 2

I've accepted ramiroaznar's answer, but I wanted to also post the solution my colleague and I ended up using, which gave us what we needed:

In the SQL window of the "Companies" dataset in Carto Builder, we wrote the following SQL, which creates an inner join between the Companies dataset and the Awards dataset, grouped by company.

SELECT c.cartodb_id, c.the_geom, c.the_geom_webmercator, c.company,
string_agg(a.award, '<br><br>') as award_field 
FROM company_list c
INNER JOIN awards_list a 
    ON c.company = a.company
GROUP BY c.cartodb_id, c.the_geom, c.the_geom_webmercator, c.company

With this we now have a new column in our Companies dataset, which contains a string list of all of the company's awards, which we can now show in our map pop-up, so that under each company name is a list, separated by line breaks, i.e. <BR><BR>, of all the awards that company received.

So, the pop-up looks something like the following. (We opted to put so much space between the awards simply because the real awards are very long, so all of the text was running together without the extra spacing):

Company Name
The Best Company

Awards
Awesome Award #1

Awesome Award #2

Awesome Award #3