I think you can do this:
ids <- dbGetQuery(conn, "SELECT id FROM my_table")
other_table <- dbGetQuery(conn, sprintf("SELECT * FROM my_other_table WHERE
t1_id in (%s)", paste(ids, collapse = ",")))
On Wed, Jun 9, 2010 at 11:24 PM, Ted Byers <r.ted.by...@gmail.com> wrote:
> I have not found anything about this except the following from the DBI
> documentation :
>
> Bind variables: the interface is heavily biased towards queries, as opposed
> > to general
> > purpose database development. In particular we made no attempt to define
> > bind
> > variables; this is a mechanism by which the contents of R/S objects are
> > implicitly
> > moved to the database during SQL execution. For instance, the following
> > embedded SQL statement
> > /* SQL */
> > SELECT * from emp_table where emp_id = :sampleEmployee
> > would take the vector sampleEmployee and iterate over each of its
> elements
> > to get the result. Perhaps the DBI could at some point in the future
> > implement
> > this feature.
> >
>
> I can connect, and execute a SQL query such as "SELECT id FROM my_table",
> and display a frame with all the IDs from my_table. But I need also to do
> something like "SELECT * FROM my_other_table WHERE t1_id = x" where 'x' is
> one of the IDs returned by the first select statement. Actually, I have to
> do this in two contexts, one where the data are not ordered by time and one
> where it is (and thus where I'd have to use TSMySQL to execute something
> like "SELECT record_datetime,value FROM my_ts_table WHERE t2_id = x").
>
> I'd like to embed this in a loop where I iterate over the IDs returned by
> the first select, get the appropriate data from the second for each ID,
> analyze that data and store results in another table in the DB, and then
> proceed to the next ID in the list. I suppose an alternative would be to
> get all the data at once, but the resulting resultset would be huge, and I
> don't (yet) know how to take a subset of the data in a frame based on a
> given value in one ot the fields and analyze that. Can you point me to an
> example of how this is done, or do I have to use a mix of perl (to get the
> data) and R (to do the analysis)?
>
> Any insights on how to proceed would be appreciated. Thanks.
>
> Ted
>
> [[alternative HTML version deleted]]
>
>
> ______________________________________________
> 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.
>
>
--
Henrique Dallazuanna
Curitiba-Paraná-Brasil
25° 25' 40" S 49° 16' 22" O
[[alternative HTML version deleted]]
______________________________________________
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.
