People and movie info in one table

sql_7 <- "SELECT n.name, ci.role_id, person_id,  movie_id, n.gender 
FROM cast_info ci
JOIN name n ON n.id = ci.person_id
WHERE role_id = '8';
"
people_info <- db %>%
  dbGetQuery(sql_7)
head(people_info)

all_info<- people_info %>%
  select(name, person_id, gender, movie_id)%>%
  group_by(name, person_id, gender, movie_id)%>%
  summarise(num=n()) 

director_info<- people_info %>%
  select(name, person_id, gender, movie_id)%>%
  group_by(name, person_id, gender, movie_id)%>%
  summarise(num=n())

movie_people_info<-movie_gross100%>%
  inner_join(all_info, by= c("movie_id"="movie_id"))

MP_info<-movie_people_info%>%
  select(title,movie_id, gross_info,production_year,gender,rating,votes, name)

jlin47 / mp4

People and movie info in one table #8