In R - Is it possible to make a mysql query with a IN, where the values in that IN are from a DataFrame column?
EXAMPLE of what I'm trying to do:
Directory = read.csv("worlddirectory.csv", header = TRUE, sep = ",",stringsAsFactors=FALSE)
Active_Customers = Directory[(Directory$Status == "Active"),]
PhoneNumbers = dbGetQuery(DBConnection,
"
Select
db.phonenumbers,
db.names
from
database db
where
db.country IN
(
Active_Customers$Country
);"
As we can see here, the expected statement looks like:
WHERE column_name IN (value1, value2, ...);
We can use paste with the argument collapse=", " to obtain the desired format. I think this should work:
PhoneNumbers = dbGetQuery(DBConnection,
paste0("SELECT db.phonenumbers, db.names ",
"FROM database db ",
"WHERE db.country IN (",
paste(Active_Customers$Country,collapse=", "),");"))
Example:
Active_Customers <- data.frame(Country=c("NL","BE","US"))
paste0("SELECT db.phonenumbers, db.names ",
"FROM database db ",
"WHERE db.country IN (",
paste(Active_Customers$Country,collapse=", "),");")
Output:
[1] "SELECT db.phonenumbers, db.names FROM database db WHERE db.country IN (NL, BE, US);"
Hope this helps!
