Subquery-error message-Different data types

Question

I run the following SQL code from a public dataset: SELECT station_id,name FROM bigquery-public-data.new_york_citibike.citibike_stations WHERE station_id IN (Select start_station_id from bigquery-public-data.new_york_citibike.citibike_trips where usertype='Subscriber') It is shown the message Cannot execute IN subquery with uncomparable types STRING and INT64. The station_id is string and start_station_id is integer. Can someone help with this please? Thanks Panos

I expected to run a query but it was shown an error message

Answer 1

I was trying to figure this out for ages taking the Google Data Analytics course, and I realized that the instructor puts in bigquery-public-data.new_york.citibike_stations INSTEAD OF bigquery-public-data.new_york_citibike.citibike_stations

Since new_york is not a dataset, it's a bit confusing. However, station_id is a STRING and start_station_id is an INT, so I did a SAFE_CAST as was suggested elsewhere for working in BigQuery instead of just CAST. None of that was in the course, so perhaps the dataset has changed since the course was made.

The following worked for me and returned the station_id and name for 'Subscribers'.

    SELECT 
      station_id,
      name
    FROM bigquery-public-data.new_york.citibike_stations
    WHERE station_id
    IN
        ((SELECT SAFE_CAST(start_station_id AS STRING) 
      FROM bigquery-public-data.new_york.citibike_trips
      WHERE usertype= 'Subscriber'))