I’m fairly new to using SQL language and can’t work out how to use a LEFT JOIN statement within a dbGetQuery() function in R. I wish to join select columns from TableB to TableA.

Here’s an example of what I’m trying to do.

filt_date <- "2024-03-11"

query <- paste0(
  "SELECT tableA.fullName, tableA.week, MAX(tableA.var1) AS var1, MAX(tableA.var2) AS var2
   FROM tableA
   WHERE testType = 'live' AND week <= '",
   filt_date,
  "'
   GROUP BY tableA.fullName, tableA.week
   LEFT JOIN (SELECT [name], [week], [bw] FROM [tableB])
   ON tableA.fullName = tableB.name AND tableA.week = tableB.week"
  )

I want to select columns name, week and bw in the second table (tableB) and join on name and week.

I get an error saying “Error: near “LEFT”: syntax error” with the current method.