Translate SQL statement in R code using dplyr-CodePudding

I need help transalting an SQL statement regarding this dataset https://www.kaggle.com/datasets/hugomathien/soccer , into r code using dplyr. The SQL statement is :

SELECT Match.date ,Team.team_long_name, Team.team_short_name ,Match.home_team_goal
FROM Team JOIN Match
ON Match.home_team_api_id = Team.team_api_id
WHERE Match.match_api_id = 492476;

The r code that i have tried is:

con <- DBI::dbConnect(RSQLite::SQLite(), "data/database.sqlite")
library(tidyverse)
library(DBI)
match<-tbl(con,"Match")
team<-tbl(con,"Team")
table_4.2<-match %>%
  filter(match_api_id=492476) %>%
  select(date,home_team_goal,home_team_api_id) %>%
  left_join(team)

and i get this error :

Error in dplyr::common_by(): ! by required, because the data sources have no common variables. Run rlang::last_error() to see where the error occurred.

Run rlang::last_error() to see where the error occurred.

CodePudding user response：

Use the code:

library(tidyverse)
Team %>%
  left_join(Match, by = c(home_team_api_id = 'team_api_id')) %>%
  filter(match_api_id == 492476) %>%
  select(date, team_long_name, team_short_name, home_team_goal)