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.
Advertisement
Answer
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)