Skip to main content
Unix & Linux

Return to Answer

brackets, I think, needed here http://stackoverflow.com/a/10538604/54964
Source Link
Léo Léopold Hertz 준영
Léo Léopold Hertz 준영
  • 7.1k
  • 30
  • 103
  • 201
df3 <- sqldf("SELECT event_value, name
   FROM file1
   WHERE data_id = 1
   LEFT JOIN 
       (SELECT data_id, name
       FROM file2
       WHERE data_id = 1)
   USING(data_id)")

df3 <- sqldf("SELECT event_value, name
   FROM file1
   WHERE data_id = 1
   LEFT JOIN 
       SELECT data_id, name
       FROM file2
       WHERE data_id = 1
   USING(data_id)")

df3 <- sqldf("SELECT event_value, name
   FROM file1
   WHERE data_id = 1
   LEFT JOIN 
       (SELECT data_id, name
       FROM file2
       WHERE data_id = 1)
   USING(data_id)")
Source Link
Léo Léopold Hertz 준영
Léo Léopold Hertz 준영
  • 7.1k
  • 30
  • 103
  • 201

sqldf approach.

One approach which shows a caveat with join approach - you cannot use WHERE data_id on both tables if you join by data_id. Code 1

file1 <- read.table("data.csv", col.names=c("data_id", "event_value"))
file2 <- read.table("log.csv", col.names=c("data_id", "name"))

library("sqldf")
df3 <- sqldf("SELECT event_value, name
   FROM file1
   LEFT JOIN file2 USING(data_id)")

df3

Output wrong because data_id = 1 should be active too

Loading required package: gsubfn
Loading required package: proto
Loading required package: RSQLite
Loading required package: tcltk
Warning message:
Quoted identifiers should have class SQL, use DBI::SQL() if the caller performs the quoting. 
  event_value    name
1 event_value    name
2         777     leo
3         666     leo
4         111 leopold
5         123    <NA>
6         324   lorem
7         245     leo

Code 2

Code

df3 <- sqldf("SELECT event_value, name
   FROM file1
   LEFT JOIN file2 USING(data_id)
   WHERE data_id = 1")

Output blank because join applied already

[1] event_value name       
<0 rows> (or 0-length row.names)

Code 3

Do WHERE earlier

df3 <- sqldf("SELECT event_value, name
   FROM file1
   WHERE data_id = 1
   LEFT JOIN file2 USING(data_id)")

Output error because two tables are of different size so WHERE should be applied on both tables

Error in rsqlite_send_query(conn@ptr, statement) : 
  near "LEFT": syntax error
Calls: sqldf ... initialize -> initialize -> rsqlite_send_query -> .Call
In addition: Warning message:
Quoted identifiers should have class SQL, use DBI::SQL() if the caller performs the quoting. 
Execution halted

Code 4

Using two SELECTs with JOIN

df3 <- sqldf("SELECT event_value, name
   FROM file1
   WHERE data_id = 1
   LEFT JOIN 
       SELECT data_id, name
       FROM file2
       WHERE data_id = 1
   USING(data_id)")

Output error

Error in rsqlite_send_query(conn@ptr, statement) : 
  near "LEFT": syntax error
Calls: sqldf ... initialize -> initialize -> rsqlite_send_query -> .Call
In addition: Warning message:
Quoted identifiers should have class SQL, use DBI::SQL() if the caller performs the quoting. 
Execution halted

Maybe, a syntax error with the second SELECT and its attachment to JOIN.

Post Made Community Wiki by Léo Léopold Hertz 준영