SQL query in R using multiple AND statements based on values in a dataframe

Question

I have been able to do this in the past using sprintf() to create an sql statement based on a list of user selected id's. But now I am wanting to make an sql statement based on a user selected list of multiple fields, from multiple tables, and I'm at a loss.

I have a database with multiple tables:

TBL1
Date  Program  Name      Type    height  width
5/22  E7       Square    angle   5       5
5/22  H9       Circle    smooth  4       4    
9/9   E7       Circle    smooth  7       7   
10/10 R8       Triangle  angle   10      5  

TBL2
Date  Program  Name      Value1  Value2
5/22  E7       Square    5       2.4
5/22  H9       Circle    10      43
9/9   E7       Circle    3.2     9
10/10 R8       Triangle  999     1

TBL3
Type    1  2  3
angle   a  g  h
smooth  b  c  d

And I have a dataframe of rows I want to get from the database

df
Date  Program  Name
5/22  E7       Square
9/9   E7       Circle
10/10 R8       Triangle

I need to dynamically generate an SQL statement for gathering the data that I need. So I would need to get the Date, Program, Name, Type, Value1, and Value2 for every Date, Program, and Name in my df.

I was trying to figure out if sqlinterpolate could handle this, but it doesn't seem like it can?

I would join TBL1 and TBL3 WHERE Type=Type, and then join the VALUES from TBL2 all only for the rows where Date, Program, and Name matched my df.

Desired output from the sql return:

Date  Program  Name      Type    1   2   3  Value1  Value2
5/22  E7       Square    angle   a   g   h  5       2.4
9/9   E7       Circle    smooth  b   c   d  3.2     9
10/10 R8       Triangle  angle   a   g   h  999     1

Thoughts?

You haven't really explained the logic by which we get from your three starting data frames to the output you want. — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 3, 2020 at 16:31
@TimBiegeleisen I'm not sure how to make it much clearer? I can do a single sql statement that does the joins across the three tables based on a single row from my dataframe. I'm just not sure how to create a dynamic statement that can do it for multiple rows. — Steve
– Steve, Commented Apr 3, 2020 at 16:57
@DavidRanzolin unfortunately no. I had read an answer where they wrote a temporary table and did the join in the database. That would be a great solution if I could use it. — Steve
– Steve, Commented Apr 3, 2020 at 17:25

David Ranzolin · Accepted Answer · 2020-04-03 17:53:33Z

You can split df into rows, create a query for each one, collapse the queries by 'UNION ALL\n', then fetch:

library(RSQLite)
library(DBI)
library(tidyverse)
library(glue)
#> 
#> Attaching package: 'glue'
#> The following object is masked from 'package:dplyr':
#> 
#>     collapse

t1 <- tibble(date = c("5/22", "5/22", "9/9", "10/10"),
             program = c("E7", "H9", "E7", "R8"),
             name = c("Square", "Circle", "Circle", "Triangle"),
             type = c("angle", "smooth", "smooth", "angle"),
             height = c(5, 4, 7, 10),
             width = c(5, 4, 5, 5))

t2 <- tibble(date = c("5/22", "5/22", "9/9", "10/10"),
             program = c("E7", "H9", "E7", "R8"),
             name = c("Square", "Circle", "Circle", "Triangle"),
             val1 = c(5, 10, 3.2, 999),
             val2 = c(2.4, 43, 9, 1))

t3 <- tibble(type = c("angle", "smooth"),
             one = c("a", "b"),
             two = c("g", "c"),
             three = c("h", "d"))

df <- tibble(date = c("5/22", "9/9", "10/10"),
             program = c("E7", "E7", "R8"),
             name = c("Square", "Circle", "Triangle"))

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "tab1", t1)
dbWriteTable(con, "tab2", t2)
dbWriteTable(con, "tab3", t3)

queries <- df %>% 
  split(seq_len(nrow(.))) %>% 
  map(~{
    d <- .x$date
    p <- .x$program
    n <- .x$name
    glue('SELECT t1.date, 
                      t1.program, 
                      t1.name,
                      t1.type,
                      t2.val1,
                      t2.val2,
                      t3.one,
                      t3.two,
                      t3.three
               FROM tab1 t1
               LEFT JOIN tab2 t2 ON t1.date = t2.date AND t1.program = t2.program
               LEFT JOIN tab3 t3 ON t1.type = t3.type
               WHERE t1.date = "{d}"
               AND t1.program = "{p}"
               AND t1.name = "{n}"')
  })
q <- paste(queries, collapse = "UNION ALL\n")
dbGetQuery(con, q)
#>    date program     name   type  val1 val2 one two three
#> 1  5/22      E7   Square  angle   5.0  2.4   a   g     h
#> 2   9/9      E7   Circle smooth   3.2  9.0   b   c     d
#> 3 10/10      R8 Triangle  angle 999.0  1.0   a   g     h

^{Created on 2020-04-03 by the reprex package (v0.3.0)}

You sir are my hero. Thank you very much. Learned about a new package too! Haven't used glue before. - I also didn't know about UNION ALL.

Collectives™ on Stack Overflow

SQL query in R using multiple AND statements based on values in a dataframe

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related