error in statement: no such column, but column exists

Question

I'm using RSQLite to combine two tables. I check every single step, but still didn't figure out what went wrong. Here is my script:

ol5_H3K4me1_mesc_common<-dbGetQuery(con,"select* from H3K4me1_mesc where H3K4me1_mesc.V2=H3K4me1_mesc_common.V2 and H3K4me1_mesc.V3=H3K4me1_mesc_common.V3")
Error in sqliteSendQuery(con, statement, bind.data) : 
  error in statement: no such column: H3K4me1_mesc_common.V2
> dbListFields(con,"H3K4me1_mesc_common")
[1] "V1" "V2" "V3"

Answer 1

In your posted query table H3K4me1_mesc_common is not part of FROM clause and so is the error. You wanted your query to look like below where t1 and t2 are table alias for respective tables

select t1.* from H3K4me1_mesc t1 
join H3K4me1_mesc_common t2
on t1.V2 = t2.V2 
and t1.V3 = t2.V3