Sorry for not sending code. I guess I was expecting a simple answer
how this sql query is translated. Like for an "and" in the "on", using
'setkey' and then doing a join works. So here is reproducible code:
require(sqldf)
table_a <- data.table(y=c("l","l","s","a","a","b","c","d","d"),
z=c("u","s","u","b","a","b","c","d","a"),
abc=c(123.2,13.5,228.4,427.2,123.1,180.2,153.8,113.2,13.2),
xyz=c("a","b","f","b","a","b","f","a","f"))
table_b <-
data.table(y=c("l","l","s","a","a","b","c","d","d","k","k","d","c","u"),
z=c("u","s","u","b","a","b","c","d","a","a","b","d","d","a"),
abc=c(123.2,13.5,228.4,427.2,123.1,180.2,153.8,113.2,13.2,123.1,180.2,153.8,113.2,13.2))
sqldf("SELECT a.xyz, sum(a.abc * b.abc)
FROM table_a as a
LEFT JOIN table_b as b
ON (a.y=b.y or a.y=b.z or a.z=b.y or a.z=b.z)
Group by a.xyz", drv="SQLite")
On Thu, Jun 6, 2013 at 1:20 PM, statquant3 <[email protected]> wrote:
> And the result you want to achieve ?
> FYI: usually it is better to send reproducible code, we can't really load
> your tables in R with what you sent us. Try ?dput or This post on SO
> <http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example>
>
>
>
> --
> View this message in context:
> http://r.789695.n4.nabble.com/Joins-with-or-condition-tp4668801p4668810.html
> Sent from the datatable-help mailing list archive at Nabble.com.
> _______________________________________________
> datatable-help mailing list
> [email protected]
> https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help
_______________________________________________
datatable-help mailing list
[email protected]
https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help
