CODEWITHSUNDEEP
sqlrdataframe
Daniel Almaraz
Daniel Almaraz

Reputation: 1

R - Dataframe values inside mysql query in R

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
);"

Upvotes: 1

Views: 53

Answers (1)

Florian
Florian

Reputation: 25375

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!

Upvotes: 2

Related Questions