SQLTeam.com | Weblogs | Forums

SQL Query to R

#1

Hello, everyone. I have a really big problem transforming an SQL query to an R command using package dplyr.

I want to transform this query: SELECT t1.team_long_name, t2.team_long_name,FROM Match
JOIN Team t1 ON Match.home_team_api_id = t1.team_api_id
JOIN Team t2 ON Match.away_team_api_id = t2.team_api_id;

The database I work with is "european soccer database", team_long_name is a variable-column of table team
and columns home_team_api_id, away_team_api_id are columns of table Match.

My attempt so far is:

home_away_goals <- match %>%
inner_join(team, by = c("home_team_api_id" = "team_api_id"), t1) %>%
inner_join(team, by = c("away_team_api_id" = "team_api_id"), t2) %>%
group_by(t1.team_long_name,t2.team_long_name)%>%
summarise()

but of course nothing happened. Please every advice will be extremelly useful. Thank you very much!!