1

I took a SQL course and am now trying to apply the lessons in practice but I can't seem to find out how to make it work.

I have two tables that contain financial information about basketball teams by year. They share the same data in the year and team columns but have a differing column each, 'team_expenses_in_dollars' and 'operating_income_in_dollars' respectively.

I'm trying to combine these two tables into one using BigQuery using INNER JOIN so that I have just one table with the columns year, team and this above.

I uploaded the separate tables into the same dataset of the same project.

SELECT 
    team_expenses.player_expenses_in_dollars, 
    team_operating_income.team_operating_income_in_dollars,
FROM 
    `player-impacts-on-teams.lebron_impact.team_expenses` 
INNER JOIN 
    `player-impacts-on-teams.lebron_impact.team_operating_income` 
        ON `player-impacts-on-teams.lebron_impact.team_expenses`.year = `player-impacts-on-teams.lebron_impact.team_operating_income`.year;

code example

I get this error

Unrecognized name: team_expenses at [1:8]

but I have checked and the name of the table and columns are correct.

What part of my code is wrong here? Or am I doing this completely wrong.

Ideally I'm hoping to combine these two tables together into one.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Neophytos Kouphou
  • 11
  • 2
  • 2
    For starters, you have a comma before `FROM`. Next, alias your table names to make this easier to read. For example, `select a.col_name from table1 a join table2 b on a.col = b.col` – Isolated Jun 27 '23 at 15:13

1 Answers1

1

The error you encountered, "Unrecognized name: team_expenses," might be an issue related to the table references in the query. When using BigQuery, you need to specify the table names without the project and dataset prefixes. Additionally, there is an extra comma after team_operating_income.team_operating_income_in_dollars that should be removed.

Here's an updated version of your query with the necessary changes:

SELECT 
    team_expenses.player_expenses_in_dollars, 
    team_operating_income.team_operating_income_in_dollars
FROM 
    `lebron_impact.team_expenses` AS team_expenses
INNER JOIN 
    `lebron_impact.team_operating_income` AS team_operating_income
    ON team_expenses.year = team_operating_income.year;

In the updated query, lebron_impact represents the dataset containing your tables, and team_expenses and team_operating_income are the aliases given to the table references for better readability. Make sure to replace lebron_impact with the actual dataset name in your BigQuery project.

This query performs an inner join on the year column to combine the team_expenses and team_operating_income tables into a single result set. The aliases team_expenses and team_operating_income are used to refer to the respective tables when selecting columns.

ps-
  • 234
  • 1
  • 6