Extracting row value based on matching columns in separate dataframes using SQL or R

Question

I have two dataframes as shown below:

Df1

date	a	b
2023-01-31	10	john
2022-02-30	15	elle
2020-03-04	20	david

Df2

date	a
2023-01-31	10
2022-02-30	30
2020-04-04	20

I want to extract the values in column b in Dataframe1 only when Df1$date = Df2$date AND Df1$a = Df2$a.

In the above example, the only row where both conditions match is row 1, so my output should be

b
john

How can I write this using sqldf in R or even using base R?

Answer 1

You can use inner_join from dplyr package

library(dplyr)
df1 %>% 
  inner_join(df2, by = c("date", "a")) %>% 
  select(b) # as per @jpsmith comment
     b
1 john