2

I'm trying to do a query in rmysql to get data from a db. I have a list of idNumbers in a column that I want to match and pull records for from a database.

R data frame: df1

idNumColumnInR        saleAmt
  345                   22.34
  456                   44.53
  678                   77.87
  ...                    ...

I think it would be something like to go from db -> R:

select * from dataBase where idNum in (df1$idNumColumnInR).

I could just use a for loop and go thru each row of the column but was curious if there's a better way.

Gavin Simpson
  • 170,508
  • 25
  • 396
  • 453
screechOwl
  • 27,310
  • 61
  • 158
  • 267
  • Are you asking how to `paste` a query like that together, or are you asking whether there's a better way than just `paste`ing it together? – joran Sep 28 '11 at 01:00

1 Answers1

3

You don't need to use a loop to use paste().

For example: 

df1 <- data.frame(idNumColumnInR=c(345,456,678), 
 saleAmt=c(22.34,44.53,77.87))

qry <- paste("SELECT * from dataBase where idNum in (",
 paste(df1$idNumColumnInR, collapse=","),")"  )
Jason B
  • 893
  • 7
  • 13