CODEWITHSUNDEEP
sqlpostgresql
murtaza52
murtaza52

Reputation: 47431

how to prevent duplicate columns in inner join with multiple select clauses

select * from
         (select date, gen_city_id, min(temp) as min_temp, max(temp) as max_temp from current_weather group by date, gen_city_id order by date) cw
         inner join
         (select gen_city_id, forecast_date, array_agg(temp) from forecast where forecast_date < current_date group by gen_city_id, forecast_date) f
         on cw.gen_city_id = f.gen_city_id and cw.date = f.forecast_date;

The above query works, however the gen_city_id and date/forecast_date columns are selected from both the tables. In my result set how do I prevent duplicate columns from both the tables ?

If I try removing the columns from the select cause of any one of the tables, then the query errors out.

Upvotes: 0

Views: 60

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use the using clause:

select *
from (select date, gen_city_id, min(temp) as min_temp, max(temp) as max_temp
      from current_weather
      group by date, gen_city_id order by date
     ) cw join
     (select gen_city_id, forecast_date as date, array_agg(temp)
      from forecast
      where forecast_date < current_date
      group by gen_city_id, forecast_date
     ) f
     using (gen_city_id, date) ;

This removes the duplicate columns included in the using clause.

In general, though, I recommend listing out all the columns separately.

Upvotes: 0

Nicola Lepetit
Nicola Lepetit

Reputation: 795

Change the query in this way. You can specify which fields you want to obtain in the resultset:

select cw.*,f.temp from
         (select date, gen_city_id, min(temp) as min_temp, max(temp) as max_temp from current_weather group by date, gen_city_id order by date) cw
         inner join
         (select gen_city_id, forecast_date, array_agg(temp) temp from forecast where forecast_date < current_date group by gen_city_id, forecast_date) f
         on cw.gen_city_id = f.gen_city_id and cw.date = f.forecast_date;

Upvotes: 1

Related Questions