How to query the data using SQL in the infowindow of CartoDB

Question

I want to use a SQL join to merge data from 2 tables and display it in the info-window. When I give my query

Select table_2.hos_name,table_2.revenue from table_1,table_2 where table_1.area_name = table_2.area_name

in the info-window editor, the Query itself is displayed instead in the info-window instead of the values. When I googled I found this link https://groups.google.com/forum/#!msg/cartodb/W5HHQx1jjMA/qBo56lghRGcJ but I couldn't relate it. Could some one point me to some relevant documentation? Should I include the query as JavaScript?

Answer 1

By default, the infowindow will display data you included in the interactivity. Generally this is limited to columns that are included in the same request you use to generate the map. If, after a user clicks, you want to request data on-the-fly, you can follow this example,

http://bl.ocks.org/javisantana/6451830

The important it is how it does the $.get request after the click event.

infowindow.set('template', function(data) {
        var clickPosLatLng = this.model.get('latlng');
        $.get('http://javi.cartodb.com/api/v1/sql?q=select * from generate_series(1, 3)', function(data) {
          var rows = _.pluck(data.rows, 'generate_series');
          $('#mylist').html(_.map(rows, function(r) { return '<li> data:' + r + '</li>' }).join('\n'));
        });

        return $('#infowindow_template').html();
      });