CODEWITHSUNDEEP
sqlbigdatagoogle-bigquerygoogle-cloud-platform
Jason Martinez
Jason Martinez

Reputation: 201

BigQuery error with joins

do you know why this wont work in bigquery?

SELECT TOP(Score.goals, 10), COUNT(*)
FROM [cloude-sandbox:public.match_goals_table] AS Score left JOIN 
     [cloude-sandbox:public.match_games_table] AS Teams
     ON Teams.team_name = Score.goals;

Query Failed Error: Join keys goals (int64) and team_name (string) have types that cannot be automatically coerced.

Upvotes: 2

Views: 1229

Answers (1)

Jeremy Condit
Jeremy Condit

Reputation: 7046

BQ requires that the join key types match exactly. You can wrap one side of the join in a subquery to perform the conversion with minimal performance cost:

SELECT TOP(Score.goals, 10), COUNT(*)
FROM
  (SELECT goals, STRING(goals) AS goals_str
   FROM [cloude-sandbox:public.match_goals_table]) AS Score
LEFT JOIN 
  [cloude-sandbox:public.match_games_table] AS Teams
ON Teams.team_name = Score.goals_str;

I selected goals from the left side of the join as both an integer and a string so that you can join on the string but compute TOP on the integer.

But did you really mean to join on the team name and the goal count? Maybe this is just an example, but that join seems unlikely to produce any output.

Upvotes: 4

Related Questions