Suspected alias issue in bigquery join

Question

I am relatively new to bigquery and think I have an aliasing problem but can't work out what it is. Essentially, I have two tables and while the first table has the majority of the required information the second table has a date of birth that I need to join. I have written the below query and the two initial SELECT statements work in isolation and appear to return the expected values. However, when attempting to join the two tables I get an error stating:

Unrecognized name: t1_teams at [10:60]

WITH table_1 AS (SELECT competition_name, stat_season_name, 
matchdata_Date, t1_teams.name, t1_players.Position, CAST(REGEXP_REPLACE(t1_players.uID, r'[a-zA-Z]', '') AS NUMERIC) AS Player_ID1, t1_players.First, t1_players.Last

FROM `prod.feed1`, 
UNNEST(teams) AS t1_teams, UNNEST(t1_teams.Players) as t1_players),

table_2 AS (SELECT t2_players.uID AS Player_ID2, t2_players.stat_birth_date
FROM `prod.feed2`,
UNNEST(players) AS t2_players)

SELECT competition_name, stat_season_name, matchdata_Date, t1_teams.name, t1_players.Position, t1_players.uID, t1_players.First, t1_players.Last, t2_players.stat_birth_date

FROM table_1
LEFT JOIN table_2
ON Player_ID1 = Player_ID2
WHERE competition_name  = "EPL"
AND stat_season_name = "Season 2018/2019"

Any help in steering me in the right direction would be greatly appreciated as my reading of the bigquery documentation and other searches have drawn a blank.

Elliott Brossard Elliott Brossard · Accepted Answer · 2018-12-19T14:10:16

The problem is here:

WITH table_1 AS (
  SELECT
    competition_name,
    stat_season_name, 
    matchdata_Date,
    -- this line
    t1_teams.name,
    ...

You're selecting t1_teams.name, so you end up with just name an an output column from the select list. If you want to refer to t1_teams later, then select that instead:

WITH table_1 AS (
  SELECT
    competition_name,
    stat_season_name, 
    matchdata_Date,
    -- this line
    t1_teams,
    ...

Suspected alias issue in bigquery join

1 Answers