RSQlite: grabbing data from a table, given values in another

Question

Let's say that I have two tables, A and B.

Table A
X  Y  Z
1  2  3
4  5  6
7  8  9

Table B
W  X  Y
3  1  4
4  4  5
7  5  9

I have identified the table A X-values of 1 and 4 as being interesting because of what is going on with Z (they're less than 8 or whatever). In table B, I want to select the W column values that have X-value 1 and 4.

I am open to two approaches.

1. Doing everything with SQL commands that I happen to call from RSQLite functions

2. Using R to form a data frame.

Since I know R reasonably well and nothing about SQL, I have been pursuing approach #2. I am easily able to select the X and Z columns with X=1 and X=4 and make a data frame.

What has not worked is when I go try to look up X=1 and X=4 in table B, if I type the value into dbGetQuery, everything works.

However, I have many values and cannot do them all by hand. dbGetQuery is not allowing me to pass the values from table A when I call them like ...WHERE "X" = str(data.frame(A)[1:2,1]). The error I am getting seems to think that I am looking for column named "1" instead of "X".

Answer 1

In pure sql, it's a straightforward join of the two tables, filtering on just the a.z values you care about:

SELECT b.w FROM b JOIN a ON b.x = a.x WHERE a.z < 8;

w
----------
3
4

Answer 2

library(dplyr)

Table_A %>% 
  filter(Z < 8) %>% 
  inner_join(., Table_B, by="X", suffix=c(".A",".B")) %>% 
  select(W)

#>   W
#> 1 3
#> 2 4

Answer 3

sqlite> .headers on
sqlite> select W from B where X in (select X from A where Z < 9);
W
3
4