Get an R dataframe with merged values from multiple MySQL tables -

i have mysql database many large tables in following format:

mysql> select * table1 limit 2; +-------+----------+-------------+ | chrom | site     | methylation | +-------+----------+-------------+ |     1 | 10003581 |          76 | |     1 | 10003584 |           0 | +-------+----------+-------------+ 

i create 1 large merged table in r contain sites covered methylation values each table. instance, if had 4 mysql tables r data frame contain following columns:

chrom    site    table1    table2    table3    table4 

so far have:

library(rmysql)  #open database mydb = dbconnect(mysql(), user='root', password='', dbname='database')  #create function values getval <- function(tablename, columnname){   rs = dbsendquery(mydb, paste("select chrom, site, methylation ", tablename))   data = fetch(rs, n=-1)   res <- rename(data, c("chrom" = "chr", "site" = "start", "methylation" = columnname))   return(res) }  table1 <- getval("table1", "table1") table2 <- getval("table2", "table2") table3 <- getval("table3", "table3") table4 <- getval("table4", "table4") 

i'd merge tables together. figure there should faster , more efficient way of doing this.

this more general, assuming number of tables you're dealing variable. renames columns way you've wanted in original function:

library(rmysql)  ##  open database: mydb = dbconnect(mysql(), user='root', password='', dbname='database')  ##  create function values: getvals <- function(tablenames) {     query <- paste0("select ", tables[1], ".chr chrom, ", tables[1], ".start site, ")     query <- paste0(query, paste0(tables, ".methylation ", tables, collapse=", "))     query <- paste0(query, " ", tables[1], paste0(" join ", tables[-1], " on ", tables[1], ".chr=", tables[-1], ".chr , ", tables[1], ".start=", tables[-1], ".start", collapse=""))    rs <- dbsendquery(mydb, query)   data <- fetch(rs, n=-1)   return(data) }  tables <- c("table1", "table2", "table3", "table4")  my_data <- getvals(tables) 

this query produced tables variable above:

> query [1] "select table1.chr chrom, table1.start site, table1.methylation table1, table2.methylation table2, table3.methylation table3, table4.methylation table4 table1 join table2 on table1.chr=table2.chr , table1.start=table2.start join table3 on table1.chr=table3.chr , table1.start=table3.start join table4 on table1.chr=table4.chr , table1.start=table4.start"