On Thu, Mar 29, 2012 at 4:28 AM, Eric Fail <eric.f...@gmx.us> wrote:
> Dear R experts,
>
> I've realized that it might not be possible to define a negative SELCET
> statement in a SQL call so now I'm looking for the smoothest way to generate
> a list of what I would like from my large database by first pulling all the
> names with a query like this "SELECT top 1 * FROM your_table" (thank you Bart
> Joosen for the idea) and then subtract the variables I am not allow to pull
> manually ending up with a 'positive' definition of what I want, something I
> can use in a SQL SELCT statement (see my email on this list from yesterday
> for more on that).
>
> When I query the database for the variable names I get something similar to
> 'DBquery' in my working example below, but considerable longer with over 2400
> hundred variables. As I only need to remove two or three variables I would
> like to define a lookup table (like the list 'lookup' in my example) and
> subtract that from my data base query. Now to my question. Is there a way I
> can subtract one list from another? Like setoff or alike?
>
> I would like to end up with a list like the one shown in my example called
> 'result.' In short, I would like to subtract 'lookup' from 'DBquery' and end
> up with 'result,' please note that 'result' is a list fo vecktors and not a
> list of dataframes. In my real life example DBquery is considerable longer so
> defining that by hand would make a really really long syntax.
>
One can use except in sqlite and in a number of other database systems:
library(sqldf)
# first two rows of BOD
BOD12 <- BOD[1:2, ]
# all rows of BOD except those in BOD12
sqldf("select * from BOD except (select * from BOD12)")
--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
