[datatable-help] Joins with or-condition

[Wolfgang Steitz]

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 <statquant at outlook.com> 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
> datatable-help at lists.r-forge.r-project.org
> https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help